Create a new Outlook message using VBA

Slipstick Systems

› Developer › Create a new Outlook message using VBA

Last reviewed on June 29, 2015     127 Comments

You can use a VBA macro to create a new message and preset any of the fields, including To/CC/BCC, the subject, expiration date, flags, voting options and more.

To use, use Alt+F11 to open the VBA editor and paste the following code into ThisOutlookSession. Remove the fields you don't want to set and edit the values in the fields you want to set automatically.

Add the macro to a toolbar or ribbon button or to the QAT.

Public Sub CreateNewMessage()
Dim objMsg As MailItem

Set objMsg = Application.CreateItem(olMailItem)

 With objMsg
  .To = "Alias@domain.com"
  .CC= "Alias2@domain.com"
  .BCC = "Alias3@domain.com"
  .Subject = "This is the subject"
  .Categories = "Test"
  .VotingOptions = "Yes;No;Maybe;"
  .BodyFormat = olFormatPlain ' send plain text message
  .Importance = olImportanceHigh
  .Sensitivity = olConfidential
  .Attachments.Add ("path-to-file.docx")

' Calculate a date using DateAdd or enter an explicit date
  .ExpiryTime = DateAdd("m", 6, Now) '6 months from now
  .DeferredDeliveryTime = #8/1/2012 6:00:00 PM#
  
  .Display
End With

Set objMsg = Nothing
End Sub

Send a new message to From address of selected messages

You can easily tweak the macro above to loop through a selection of messages and send a new message to the senders.

To use, select one or more messages then run the macro. As written, it opens the messages so you can review them and send yourself. You can change .display to .Send if you want to send them automatically. (Use .display when testing.)

Public Sub CreateNewMessage()
Dim objMsg As MailItem
Dim Selection As Selection
Dim obj As Object

Set Selection = ActiveExplorer.Selection

For Each obj In Selection

Set objMsg = Application.CreateItem(olMailItem)

 With objMsg
  .To = obj.SenderEmailAddress
  .Subject = "This is the subject"
  .Categories = "Test"
  .Body = "My notes" & vbcrlf & vbcrlf & obj.Body 
  .Display
' use .Send to send it automatically 

End With
Set objMsg = Nothing

Next

End Sub

Create a new contact with some fields filled in

You can use the same method with other Outlook items. This example creates a new contact with a country and city and part of phone number.

Use TaskItem and olTaskItem for Tasks, AppointmentItem and olAppointmentItem for appointments. You'll need to replace the fields with the correct properties for the item type. You can get the property names from VBA Help or at MSDN.

Public Sub CreateNewContact()
Dim objContact As ContactItem

Set objContact = Application.CreateItem(olContactItem)

 With objContact 
  .BusinessAddressCity = "Halifax"
  .BusinessAddressCountry = "Canada"
  .Business2TelephoneNumber = "902123" 'the area code and local prefix
  .Display
End With

Set objContact = Nothing
End Sub

Create a new Appointment

This macro creates a new appointment with the Location field filled in. Other fields can be added to it and if you need an meeting, click Invite attendees on the ribbon.


Sub CreateApptLocation()

Dim olAppt As AppointmentItem
Set olAppt = Application.CreateItem(olAppointmentItem)

With olAppt
   .Subject = "My Subject"
   .Location = "My Favorite place"
   .Categories = "Business"
   .Display
End With
End Sub
Post Views: 38

About Diane Poremsky

A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Comments

  2. Jimmy says

    Hello Diane
    Thanks for this great post. Wondering if you can help - I receive emails with the subject and attachments as i want them (from our accounting software). I am wondering if i could have a macro set to a button that
    a) opens a forward draft (so i can add the clients name)
    b) adds certain text to the body of the draft (generally the same on each one)

    Even better would be to open a NEW email and copy across the attachments and subject of the email i get from the system so that i dont have to delete traces of initial email.

    Reply

  3. DivEff says

    Thank you very much for this article! It helped me to create new email object where I can copy extracted text from multiple emails into it.

    Reply

  4. Sahil Bhargava says

    Hi,
    I want to modify my .To and .Cc based on the following conditions:
    .To : Ops MANAGER
    .Cc: Managers(under him)

    Example:
    I have the following columns with me:
    Ops Manager 1 Manager 1
    Ops Manager 1 Manager 2
    Ops Manager 2 Manager 3
    Ops Manager 2 Manager 4
    Ops Manager 3 Manager 5

    .To : Ops Manager 1
    .Cc : Manager 1, Manager 2

    Reply

    • Diane Poremsky says

      For things like that, i recommend using multiple macros - ones with the addresses as strings that calls the main macro and passes the values.

      Dim strTo As String
      Dim strCC As String

      Public Sub ToMgr1()
      strTo = "manager1@domain.com"
      strCC = "alias@domain.com;alias2@domain.com"
      CreateNewMessage
      End Sub

      Public Sub ToMgr2()
      strTo = "manager2@domain.com"
      strCC = "alias3@domain.com;alias4@domain.com"
      CreateNewMessage
      End Sub

      Private Sub CreateNewMessage()
      Dim objMsg As MailItem

      Set objMsg = Application.CreateItem(olMailItem)

      With objMsg
      .To = strTo
      .CC = strCC
      .Subject = "This is the subject"
      .Display
      End With

      Set objMsg = Nothing
      End Sub

      Reply

      • Diane Poremsky says

        To do something like this, where it is dynamic and the To changes? You would need to use multiple macros - the macro needs to add the addresses before opening.

        Ops Manager 1 Manager 1
        Ops Manager 1 Manager 2
        Ops Manager 2 Manager 3
        Ops Manager 2 Manager 4
        Ops Manager 3 Manager 5

        .To : Ops Manager 1
        .Cc : Manager 1, Manager 2

        You can use a "stub macro" that sets the values and then calls the main one -
        Dim strTo as string
        Dim strCC as string
        Dim strBCC as string

        Public sendToBob()
        strTo = "op1@domain.com"
        strCC = "mrg1@domain.com"
        strBCC = "address@domain"
        CreateNewMessage
        End Sub

        Public sendToMary()
        strTo = "op2@domain.com"
        strCC = "mrg2@domain.com"
        strBCC = "address@domain"
        CreateNewMessage
        End Sub

        Private Sub CreateNewMessage()
        Dim objMsg As MailItem

        Set objMsg = Application.CreateItem(olMailItem)

        With objMsg
        .To = strTo
        .CC= strCC
        .BCC = strBCC
        .Subject = "This is the subject"
        (rest of macro snipped)

  5. Gabor Roth says

    Hi,
    I'd like to create e-mails automatically sent out using the following criteria:
    If end of the month is Monday or Thursday.
    Is it possible somehow?
    Would be great if it also worked when Outlook is not opened.

    thanks in advance,
    Gabor

    Reply

    • Diane Poremsky says

      Sending with outlook closed would require powershell (and it would open outlook).

      You're wanting to send if the 30th is a mon or thurs? (or 31 or 28/29) I would check to see if date + 1 = 1 then check if today was mon or thurs.
      Something like DateSerial(Month(Date) + 1) = 1
      then check the day name:
      WeekdayName(Weekday(Now())) = "Monday" Or WeekdayName(Weekday(Now())) = "Thursday" Then

      I have a macro that should point you in the right direction at
      https://www.slipstick.com/developer/code-samples/delay-sending-messages-specific-times/

      Reply

  6. chucky says

    Although I am not a newb at programming here is a funny story and warning too. If you try all this send mail code, do not test it with the email account Outlook is set too or you will spend a week and many hours sifting through the internet trying to figure out why the mail is being sent to the outbox and not actually sending the email, there is no help on that. I accidentally figured it out myself when I sent some mail not via code to the wrong address, namely my own. It went to the outbox, I was like WTF, all that time I spent trying to figure it out. It would have been nice if there was some documentation provided saying "hey, if you send mail to yourself it will go to the outbox". I hope you had fun laughing :)

    Reply

    • Diane Poremsky says

      >> "hey, if you send mail to yourself it will go to the outbox".
      If the code sends the message, it should send it and come back to the inbox :)

      Some of the macro samples display the messages instead of actually sending them - this is so you can see what they look like without actually spamming someone (my sample addresses) or yourself.

      Reply

  7. Mark says

    Hi, I'm learning how to write Microsoft outlook VBA code from scratch. Where should I able begein to write code? Thanks!

    Reply

    • Diane Poremsky says

      Unlike the other office apps, Outlook doesn't have a macro recorder, so you need to write it yourself or work with macros you find online. You'll write / edit it in the VBA editor.

      Reply

  9. Andres Meluk says

    Thank thank you thank you. I really needed this at work.

    Can we create a code where:
    if email contains "send file 400" then
    .To = "Alias@domain.com"
    .CC= "Alias2@domain.com"
    .BCC = "Alias3@domain.com"
    .Subject = "This is the subject"
    .Categories = "Test"
    .Attachments.Add ("path-to-file.docx")
    .send
    end if

    Reply

  11. Nicko says

    Hi there,
    I want to embed an image into an email with a links in it. I mean, an image behind a text/links

    Reply

    • Diane Poremsky says

      Use HTMLbody - you don't need to use strings for the html (just put it in the htmlbody line, but it can make it easier to read.
      strLink = "<a href='https://www.slipstick.com'>"
      strLinkText = "<img alt='' height='36' src='https://www.slipstick.com/images/sliplogo.gif' width='150' /></a>"

      oMail.BodyFormat = olFormatHTML
      oMail.HTMLBody = strLink & strLinkText

      Reply

      • Diane Poremsky says

        You can also use Word code to insert it -
        Set oMail = Application.CreateItem(olMailItem)
        Set objInsp = oMail.GetInspector
        Set objDoc = objInsp.WordEditor
        Set objSel = objDoc.Windows(1).Selection

        objSel.InlineShapes.AddPicture FileName:="https://www.slipstick.com/images/sliplogo.gif"
        objSel.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
        objDoc.Hyperlinks.Add Anchor:=objSel.Range, Address:="https://www.slipstick.com"

  12. Elio Fernandes says

    Hi Diane,

    I would like to know if it is possible to create a link inside a email message that allow to send an sms. Something like this: <a href="+2025554567">Send SMS to us </a>.

    Regards,
    Elio Fernandes

    Reply

  13. Zalt Mooden says

    Hi,

    I needed to create a basic macro but Outlook family looks very picky : OTL2003 is like that and OTL2007 is like this ...

    So, I copy your script and removed many lines since my need is very straightforward. : I need to create macro for a variable set of pre-addressed recipients. So, I will have a button for each of these recipients which will accelerate my work.

    But, my OTL2007 refused to run it. I carefully repeat the sequence but no success even I am not a VBA programmer. I tried your full script and again no success even I Enable macros for my Office set and Outlook as well.

    As you will see, my set of parameters is simple compared to your initial one.

    01 Let say that I need to create a macro named Zalt_To_JSmith.
    02. The target email address is JSmith1234@gmail.com
    03. Importance Is set to HIG.
    04. Sensitivity is set to NORMAL
    05. I use the HTLM format for my emails
    06. I will set my deferred date and time manually.
    07. No more parameters.

    So, what will become my reduced script ; even I removed many lines , my OTL2007 is still bugging. I put some ????? when I am ignoring what will become that code line.

    The result should be a macro that I will associated to a button which will trigger the script for this recipient then another macro for another one , etc..

    Waiting for your help.

    Zalt. 2017-02-25 16h50.

    __________________________
    Public Sub CreateNewMessage()
    Dim objMsg As MailItem

    Set objMsg = Application.CreateItem(olMailItem)

    With objMsg
    .To = "Alias@domain.com"
    .BodyFormat = ol????? ' I need HTLM format
    .Importance = olImportanceHigh
    .Sensitivity = I need NORMAL what will be the code ?????

    .Display
    End With

    Set objMsg = Nothing
    End Sub

    Reply

    • Diane Poremsky says

      Does this work? It definitely should work in all versions of Outlook.
      Public Sub CreateNewMessage()
      Dim objMsg As MailItem
      Set objMsg = Application.CreateItem(olMailItem)
      With objMsg
      .To = "Alias@domain.com"
      .Display
      End With
      Set objMsg = Nothing
      End Sub

      If your default mail format is html, the format will be html, no need to set it. Same for sensitivity - you only need to set it if it's not Normal.
      Bodyformat options are olFormatHTML, olFormatPlain, olFormatRichText
      Sensitivity options are olConfidential, olNormal, olPersonal, olPrivate

      Reply

  14. Thomas Ampessan says

    i make a little modification in the code, but i dont know how to put the word "Hello" in bold format. Can u help me please?

    Public Sub CreateNewMessage()
    Dim objMsg As MailItem

    Set objMsg = Application.CreateItem(olMailItem)

    With objMsg
    .To = "test@test.com"
    .CC = "test1@test.com.br"
    .Subject = "Description"
    .BodyFormat = olFormatPlain
    .Body = "Testing," & vbCrLf & vbCrLf & "Hello"
    .Display
    End With

    Set objMsg = Nothing
    End Sub

    Reply

    • Diane Poremsky says

      You'll use HTML code - Hello.

      Heh... of course wordpress converted it to the finished html. :) <strong>Hello</strong>

      Reply

  15. richard says

    I want to automate sending an email to a fixed address when a file is transferred to a particular folder (I plan to use Watch 4 Folder to monitor and execute the app). Can your code be used in this manner?

    Reply

    • Diane Poremsky says

      I'm not familiar with that application, but as long as it can trigger a macro, it would work. It looks like you might be able to trigger a .vbs script with it, if so, it can work. The macro needs tweaked to work in a vbscript.

      Reply

    • Diane Poremsky says

      Use this format:
      strTemplate = "C:\Users\me\Templates\" & strTemplate & ".oft"
      Set oMail = Application.CreateItemFromTemplate(strTemplate)

      Reply

      • DannyB says

        I want to do something similar; I need to open an existing template, from the "User Templates in the File System", from the Tools > Forms > Choose form menu. The form name is Introduction.oft

      • DannyB says

        This is what I have but nothing happens when I run it:

        Public Sub CreateNewMessage()

        strTemplate = "C:\Users\Danny\Documents\Outlook\Template\" & "Introduction" & ".oft"
        Set oMail = Application.CreateItemFromTemplate(strTemplate)

        End Sub

        What am I doing wrong?

      • Diane Poremsky says

        If you aren't using multiple templates with the code, just use
        strTemplate = "C:\Users\Danny\Documents\Outlook\Template\Introduction.oft"

        You are missing oMail.display - you load the macro, but aren't displaying it.

  18. Mederic says

    I try to forward a mail that I receive from a specific address, I am using the first rule of outlook, however the following script do not allow me to display the mail , is there any way to forward a received mail and attached a file to it and display it before sending it ?

    Sub ForwardEmail(item As Outlook.MailItem)
    Dim oMail As MailItem

    On Error GoTo Release

    If item.Class = olMail Then
    Set oMail = item.Forward
    oMail.Subject = oMail.Subject
    oMail.HTMLBody = "Have a nice day." & vbCrLf & oMail.HTMLBody
    oMail.Recipients.Add "jjj.ggg@kk.com"
    oMail.Attachments.Add "O:\log File.xlsx"
    oMail.Save
    oMail.Send '<= .Display ??
    End If
    MsgBox "done"
    Release:
    Set oMail = Nothing
    Set oExplorer = Nothing
    End Sub

    Reply

  19. Sergio says

    Hi Diane, thanks for you code.
    If I use it in a Outlook macro, it works very well but
    I am tring to use it in Access and it does not work. I added all the outlook references, but no way.
    Could you help me ?
    Thanks, Sergio.

    Reply

    • Diane Poremsky says

      Any error messages? Comment out error handling so you can see where it quits.

      With the reference set to Outlook in Access VBA, this should work:
      Sub sendEmail()
      Dim oApp As Outlook.Application
      Dim oMail As MailItem
      Set oApp = CreateObject("Outlook.application")

      Set oMail = oApp.CreateItem(olMailItem)
      With oMail
      .Body = "Say something"
      .Subject = "Test"
      .To = "alias@domain.com"
      .Send
      End With
      Set oMail = Nothing
      Set oApp = Nothing

      End Sub

      Reply

  20. chengwei says

    It seems like im hardcoding the exisiting email addresses into the VBA code. Is there anyway i can make it more dynamic so that if i add say, a new email address and name, it will still work properly and send the email to the new addition?

    Reply

    • Diane Poremsky says

      You can. .To adds one address, if you want to add more addresses, you use .recipients.add ("alias@address"). You can also pass variables -
      .To = strAdress

      Reply

  21. Phil Dapaah says

    Hello Diane, Thank you for the tips.
    I am looking to find out how to edit the "from" status of my emails (i.e who the email is sent from and who it may be replied to). I am using vba to send automated emails but unsure how to resolve this issue. Would you know how I should approach this? Thanks in advance!

    Reply

  22. Kamille Domingo says

    Hello Dianne,

    I do not have any technical background but I am starting to learn vba and macros. I am currently working with a team with no automated e-mail template generator.

    I want to automate the process where I have all the templates, operators will just have an excel file and choose on a dropdown list (which e-mail template to use), be able to edit it and send it straight to outlook.

    Please help me.
    Thank you.

    Reply

  23. Jon L. says

    Hello Diane and thank you for all of the info provided. An issue I would like to solve when creating an outlook email through VBA is that the email window that opens becomes "modal" and I cannot switch to any other office application until it's sent or cancelled. Is that a limitation / feature or can it be changed? MS Access & Outlook 2010

    Reply

  24. RMH says

    Hi Diane,
    I have a Excel SendMail Outlook file using VBA (Ron DeBruin's code) that works perfectly... except I want to save the file attachment each time into a specified folder, like C:\Users\Me\Desktop. Currently, the attachment is sent and not found anywhere. I can attach the code if that's helpful. I feel like I am so close, but I don't know where or how to modify the code. Thank you in advance.

    Private Sub RDB_Outlook_Click()
    Dim StringTo As String, StringCC As String, StringBCC As String
    Dim ShArr() As String, FArr() As String, strDate As String
    Dim myCell As Range, cell As Range, rng As Range, Fname As String, Fname2 As String
    Dim wb As Workbook, sh As Worksheet
    Dim DefPath As String
    Dim olApp As Object
    Dim olMail As Object
    Dim FileExtStr As String
    Dim FileExtStr2 As String
    Dim FileFormatNum As Long

    Dim ToArray As Variant
    Dim CCArray As Variant
    Dim BCCArray As Variant

    Dim StringFileNames As String
    Dim StringSheetNames As String
    Dim FileNamesArray As Variant
    Dim SheetNamesArray As Variant
    Dim I As Long, S As Long, F As Long
    Dim WrongData As Boolean

    If Me.ProtectContents = True Or ActiveWindow.SelectedSheets.Count > 1 Then
    MsgBox "This macro will not work if the RDBMailOutlook worksheet is " & _
    "protected or if you have more then sheet selected(grouped)", 48, "RDBMailOutlook"
    Exit Sub
    End If

    'Set folder where we save the temporary files
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) "\" Then
    DefPath = DefPath & "\"
    End If

    'Set reference to Outlook and turn of ScreenUpdating and Events
    Set olApp = CreateObject("Outlook.Application")
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    'Set cells with Red interior color to no fill(cells with wrong data)
    Range("A6").ListObject.DataBodyRange.Interior.Pattern = xlNone

    'Set rng to the first column of the table
    Set rng = Me.Range("A6").ListObject.ListColumns(1).Range

    For Each myCell In rng

    'Create mail if "Yes" in column A
    If LCase(myCell.Value) = "yes" Then

    StringTo = "": StringCC = "": StringBCC = ""
    S = 0: F = 0
    Erase ShArr: Erase FArr

    'Set Error Boolean to False
    WrongData = False

    'Check if there are Sheet names in column B

    'If B is empty S = 0 so you not want to send a sheet or sheets
    If Trim(Me.Cells(myCell.Row, "B").Value) = "" Then S = 0

    'If there are sheet names in the B column S is the number of sheets it add to the Array
    If LCase(Trim(Me.Cells(myCell.Row, "B").Value)) "workbook" Then
    StringSheetNames = Me.Cells(myCell.Row, "B").Value
    SheetNamesArray = Split(StringSheetNames, Chr(10), -1)

    For I = LBound(SheetNamesArray) To UBound(SheetNamesArray)
    On Error Resume Next
    If SheetNamesArray(I) "" Then
    If SheetExists(CStr(SheetNamesArray(I))) = False Then
    Me.Cells(myCell.Row, "B").Interior.ColorIndex = 3
    WrongData = True
    Else
    S = S + 1
    ReDim Preserve ShArr(1 To S)
    ShArr(S) = SheetNamesArray(I)
    End If
    End If
    On Error GoTo 0
    Next I
    Else
    'If you only enter "workbook" in colomn B to mail the whole workbook S = -1
    S = -1
    End If

    'Check to Mail addresses in column F
    If Trim(Me.Cells(myCell.Row, "F").Value) "" Then
    StringTo = Me.Cells(myCell.Row, "F").Value
    ToArray = Split(StringTo, Chr(10), -1)
    StringTo = ""

    For I = LBound(ToArray) To UBound(ToArray)
    If ToArray(I) Like "?*@?*.?*" Then
    StringTo = StringTo & ";" & ToArray(I)
    End If
    Next I
    End If

    'Check to Mail addresses in column G
    If Trim(Me.Cells(myCell.Row, "G").Value) "" Then
    StringCC = Me.Cells(myCell.Row, "G").Value
    CCArray = Split(StringCC, Chr(10), -1)
    StringCC = ""

    For I = LBound(CCArray) To UBound(CCArray)
    If CCArray(I) Like "?*@?*.?*" Then
    StringCC = StringCC & ";" & CCArray(I)
    End If
    Next I
    End If

    'Check to Mail addresses in column H
    If Trim(Me.Cells(myCell.Row, "H").Value) "" Then
    StringBCC = Me.Cells(myCell.Row, "H").Value
    BCCArray = Split(StringBCC, Chr(10), -1)
    StringBCC = ""

    For I = LBound(BCCArray) To UBound(BCCArray)
    If BCCArray(I) Like "?*@?*.?*" Then
    StringBCC = StringBCC & ";" & BCCArray(I)
    End If
    Next I
    End If

    If StringTo = "" And StringCC = "" And StringBCC = "" Then
    Me.Cells(myCell.Row, "F").Resize(, 3).Interior.ColorIndex = 3
    WrongData = True
    End If

    'Check the other files that you want to attach in column J
    If Trim(Me.Cells(myCell.Row, "J").Value) "" Then
    StringFileNames = Me.Cells(myCell.Row, "J").Value
    FileNamesArray = Split(StringFileNames, Chr(10), -1)

    For I = LBound(FileNamesArray) To UBound(FileNamesArray)
    On Error Resume Next
    If FileNamesArray(I) "" Then
    If Dir(FileNamesArray(I)) "" Then
    If Err.Number = 0 Then
    F = F + 1
    ReDim Preserve FArr(1 To F)
    FArr(F) = FileNamesArray(I)
    Else
    Err.Clear
    Me.Cells(myCell.Row, "J").Interior.ColorIndex = 3
    WrongData = True
    End If
    Else
    Me.Cells(myCell.Row, "J").Interior.ColorIndex = 3
    WrongData = True
    End If
    End If
    On Error GoTo 0
    Next I
    End If

    'Not create the mail if there are Errors in the row (wrong sheet or file names or no mail addresses)
    If WrongData = True Then GoTo MailNot

    strDate = Format(Now, "dd-mmm-yyyy")

    'Copy the sheet(s)to a new workbook
    If S > 0 Then
    ThisWorkbook.Sheets(ShArr).Copy
    Set wb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    If Val(Application.Version) < 12 Then
    'You use Excel 97-2003
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    Select Case ThisWorkbook.FileFormat
    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
    Case 52:
    If wb.HasVBProject Then
    FileExtStr = ".xlsm": FileFormatNum = 52
    Else
    FileExtStr = ".xlsx": FileFormatNum = 51
    End If
    Case 56: FileExtStr = ".xls": FileFormatNum = 56
    Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
    End Select
    End If

    Fname = DefPath & Trim(Me.Cells(myCell.Row, "C").Value) & _
    " " & strDate & FileExtStr
    End If

    'You enter only "workbook" in colomn B to mail the whole workbook
    'Use SaveCopyAs to make a copy of the workbook
    If S = -1 Then
    FileExtStr2 = "." & LCase(Right(ThisWorkbook.Name, _
    Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, ".", , 1)))
    Fname2 = Defpath & Trim(Me.Cells(myCell.Row, "C").Value) & _
    " " & strDate & FileExtStr2

    ThisWorkbook.SaveCopyAs Fname2
    Me.Activate
    Set wb = Workbooks.Open(Fname2)
    Application.DisplayAlerts = False
    wb.Sheets(Me.Name).Delete
    Application.DisplayAlerts = True
    If wb.Sheets(1).Visible = xlSheetVisible Then wb.Sheets(1).Select
    End If

    'Make values of your formulas if you enter yes in the Values column.
    'Delete all objects if you enter yes in the delete objects column.
    'If you only want to delete Forms or ActiveX controls see
    'https://www.rondebruin.nl/controlsobjectsworksheet.htm
    If S 0 Then
    If LCase(Me.Cells(myCell.Row, "D").Value) = "yes" Or _
    LCase(Me.Cells(myCell.Row, "E").Value) = "yes" Then

    For Each sh In wb.Worksheets
    If sh.Visible = xlSheetVisible Then
    sh.Select
    If sh.ProtectContents = False Then

    If LCase(Me.Cells(myCell.Row, "D").Value) = "yes" Then
    With sh.UsedRange
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
    End With
    Application.CutCopyMode = False
    End If
    If LCase(Me.Cells(myCell.Row, "E").Value) = "yes" Then
    On Error Resume Next
    sh.DrawingObjects.Visible = True
    sh.DrawingObjects.Delete
    On Error GoTo 0
    End If

    ElseIf sh.ProtectContents = True Then

    On Error Resume Next
    sh.Unprotect Trim(Me.Range("C4").Value)
    On Error GoTo 0
    If sh.ProtectContents = False Then

    If LCase(Me.Cells(myCell.Row, "D").Value) = "yes" Then
    With sh.UsedRange
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
    End With
    Application.CutCopyMode = False
    End If

    If LCase(Me.Cells(myCell.Row, "E").Value) = "yes" Then
    On Error Resume Next
    sh.DrawingObjects.Visible = True
    sh.DrawingObjects.Delete
    On Error GoTo 0
    End If

    sh.Protect Trim(Me.Range("C4").Value)
    Else
    Me.Cells(myCell.Row, "D").Resize(, 2).Interior.ColorIndex = 3
    WrongData = True
    End If
    End If
    End If
    Next sh
    If wb.Sheets(1).Visible = xlSheetVisible Then wb.Sheets(1).Select
    End If

    'There password is not correct for all sheets, not possible to make values
    'or delete objects. we not create this mail.
    If WrongData = True Then
    wb.Close False
    If S = -1 Then
    Kill Fname2
    End If
    Set wb = Nothing
    GoTo MailNot
    End If

    If S = -1 Then
    wb.Save
    Else
    wb.SaveAs Fname, FileFormatNum
    End If
    wb.Close False
    Set wb = Nothing
    End If

    On Error Resume Next
    Set olMail = olApp.CreateItem(0)
    With olMail
    .To = StringTo
    .CC = StringCC
    .BCC = StringBCC
    .Subject = Me.Cells(myCell.Row, "I").Value
    .Body = Me.Cells(myCell.Row, "K").Value

    If S > 0 Then .Attachments.Add Fname
    If S = -1 Then .Attachments.Add Fname2

    If F > 0 Then
    For I = LBound(FArr) To UBound(FArr)
    .Attachments.Add FArr(I)
    Next I
    End If

    'Set Importance 0 = Low, 2 = High, 1 = Normal
    If LCase(Me.Cells(myCell.Row, "L").Value) = "yes" Then
    .Importance = 2
    End If

    'Display the mail or send it directly, see cell C3
    If LCase(Me.Range("C3").Value) = "yes" Then
    .Display
    Else
    .Send
    End If
    End With

    If S > 0 Then Kill Fname
    If S = -1 Then Kill Fname2
    On Error GoTo 0

    Set olMail = Nothing
    End If

    MailNot:
    Next myCell

    If LCase(Me.Range("C3").Value) = "no" Then
    MsgBox "The macro is ready and if correct the mail or mails are created." & vbNewLine & _
    "If you see Red cells in the table then the information in the cells is " & vbNewLine & _
    "not correct. For example there is a sheet or filename that not exist." & vbNewLine & _
    "Note: It will not create a Mail of the information in a row with a " & vbNewLine & _
    "Red cell or cells.", 48, "RDBMailOutlook"
    End If

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    Set olApp = Nothing
    End Sub

    Function SheetExists(wksName As String) As Boolean
    On Error Resume Next
    SheetExists = CBool(Len(ThisWorkbook.Sheets(wksName).Name) > 0)
    On Error GoTo 0
    End Function

    Private Sub BrowseAddFiles_Click()
    Dim Fname As Variant
    Dim fnum As Long

    If ActiveCell.Column = 10 And ActiveCell.Row > 6 Then
    Fname = Application.GetOpenFilename(filefilter:="All Files (*.*), *.*", _
    MultiSelect:=True)
    If IsArray(Fname) Then
    For fnum = LBound(Fname) To UBound(Fname)
    If fnum = 1 And ActiveCell.Value = "" Then
    ActiveCell.Value = ActiveCell.Value & Fname(fnum)
    Else
    If Right(ActiveCell, 1) = Chr(10) Then
    ActiveCell.Value = ActiveCell.Value & Fname(fnum)
    Else
    ActiveCell.Value = ActiveCell.Value & Chr(10) & Fname(fnum)
    End If
    End If
    Next fnum

    With Me.Range("J1").EntireColumn
    .ColumnWidth = 255
    .AutoFit
    End With
    With Me.Rows
    .AutoFit
    End With
    End If
    Else
    MsgBox "Select a cell in the ""Attach other files"" column", 48, "RDBMailOutlook"
    End If
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 5 And Target.Column 6 Then
    With Range(Target.Address)
    .Hyperlinks.Delete
    End With
    End If
    End Sub

    Reply

    • Diane Poremsky says

      it looks like the file is saved here:
      ThisWorkbook.SaveCopyAs Fname2

      This line adds the attachment to the message:
      If S > 0 Then .Attachments.Add Fname
      If S = -1 Then .Attachments.Add Fname2

      add these two lines after those lines to see the full path.
      debug.print fname
      debug.print fname2

      see https://www.slipstick.com/developer/save-attachments-to-the-hard-drive/ for code samples to save attachments - i think you'll need to save the message then save the attachment - or, if you have the full path to fname/fname2, copy it to the new location.

      Reply

  26. Sergio Souza says

    Diane, I am using a code that I got from you a while back. My machine was upgraded to WIN 10 and now something strange is happening. The VBA code is not grabbing my list of emails from my Access DB as it was before....
    My DB has the fields PAX, Email, etc.. as you will see. Everything come on when I press my button command to send an email, except the emails I have listed under EMAIL on the DB.
    Any help will be appreciated!!!

    here is my code:

    'send email about new invoice

    Private Sub Email_New_Invoice_Click()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    'Dim filepath As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    OutApp.Session.Logon
    OutApp.ActiveWindow.Activate

    strbody = "Referente PAX: " & Me.PAX & " - Reserva: " & Me.Reservation & ""

    On Error Resume Next

    With OutMail
    'oOutlookApp = Globals.ThisAddIn.Application
    .Display
    .To = Me.Email
    .Cc = ""
    .BCC = ""
    .Subject = Me.PAX & " - Fatura para Pagamento"
    .HTMLBody = strbody & "" & .HTMLBody
    'Find out if there is a file linked to this case

    If filepath "" Then
    .Attachments.add (filepath.Value)
    Me.WebBrowser34 = filepath
    .ReadReceiptRequested = True
    Me.WebBrowser34 = filepath
    'Application.FollowHyperlink (filepath)
    .Display

    Else
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    End Sub

    Reply

  27. Juan Zapata says

    Hi Diane,

    I am having the following issue. I got this macro to replace text, in this case APPOINMENTDATE, with the actual date of the appointment, which is entered in a text field of the form, this works just fine except that the body of the message loses its format. I have some pictures in the template and some text in bold, but after running the macro, I lose the pictures and the format. Is there a way to perform the same task without changing the format of the body? Your help is greatly appreciated

    Private Sub OK_Click()

    Dim objMsg As MailItem
    Dim body As String

    Set objMsg = Application.ActiveInspector.CurrentItem

    body = objMsg.body
    body = Replace(body, "APPOINTMENTDATE", Me.appdate)
    objMsg.body = body

    End Sub

    Reply

  28. Mads Nørregaard-Madsen says

    Hi Diane,

    I have written a VB Add-in for Outlook that check mail recipients before an email is send. It works fine when writing a new mail or replying to pop-out messages in Outlook2013 but I can't get it to work when replying to email using the in-line editor.

    I have tried with

    Active_Window = Application.ActiveWindow

    and

    Active_Window = Application.ActiveInspector

    But it doesn't catch the currently edited mail

    Do have any suggestion to how I might set an in-line edited mail as the CurrentItem?

    Reply

    • Mads Nørregaard-Madsen says

      Figured it out:

      olMailitem = myolApp.Application.ActiveExplorer.Selection.Item(1)

      Catches all instances of replying :)

      Reply

  29. Shay says

    Hi Diane,

    I'm writing a macro to do a similar task. I need my "submit" button to send the excel file as a PDF via email with Lotus Notes. I have it set to do almost everything, but I want the email address filled in in the "To" line. Is this possible?

    Reply

    • Diane Poremsky says

      Are you using Outlook as the mail client? If so, use .To = "the-addres@domain.com". Depending on your code, you might need to add the mail object you're using before the .to (olMail.To = ).

      If you are using the lotus notes client, I have no idea. Sorry.

      Reply

  30. Jeanne Dixon says

    Hi Diane. I hope you can point me in the right direction regarding embedding code into an email. I would like to create an email (already composed in vba) that includes either 2 radio or regular buttons. One button would just send a confirmation reply to the sender. I would like the other button to prompt the user for some information, then send that data to the sender.

    How would I go about doing this?

    Reply

    • Diane Poremsky says

      That is not possible in a message - you'd need to use a custom form, but custom forms only work with other outlook users and work best within an Exchange server organization. The other option is an HTML form, but outlook doesn't support active content, which is what an HTML form would be. Sorry.

      Reply

  31. Naphtali says

    Hi Diane,

    Thank you very much for your your assistance and time. I am an executive assistant and I was hoping there was a way to create a macro that runs every time my boss creates a new email.

    My end goal is that every time he creates an email my email address automatically populates in the CC field - but can be removed if he desired.

    Is that possible?

    Thank you so much in advance!

    Reply

  32. Ed Cuttle says

    lots of good info, here is my issue. I have a Access program that I call SendEmailOutlook() what I do is extract an email address from a database and add that to the call statement, but sometimes the email address is wrong so I get an MS Access Error Message "Outlook does not recognize one or more names - Error Number -2147467259"

    What I want to do is when this happens is pop up a message box that says 'EMAIL IS INVALID", let the user acknowledge this and let the program continue. Any ideas?

    Reply

    • Diane Poremsky says

      You'd add an error handler - one way is when an error is found, it goes to a different part of the macro, where the popup is called.

      On Error GoTo PopUp
      PopUp:
      msgbox "invalid address"
      err.clear

      Reply

  33. Jagdev says

    Hi Diane

    Is it possible to match the name in the ID and in the mail body. In the below example both the names are same in case if it is different then a msg should pop-up that the names are different.

    Ex -

    ID - JagdevSingh

    Body

    Hi Jagdev

    Reply

    • Diane Poremsky says

      As long as you can grab the name in the body, anything is possible. You'd use regex to get the name from the body and compare it to the ID.

      Reply

  34. Sanjay says

    Hi Diane,
    You've been amazing and very generous with your advice and time. I have a question. In outlook, I am creating an email using vba. I want to attach another email to this email. How do i do that? thanks for all your help

    Reply

    • Diane Poremsky says

      Assuming you are forwarding the selected message, you need to identify it:
      Dim objFWD As MailItem
      Set objFWD = Application.ActiveExplorer.Selection.Item(1)

      Then attach it - instead of using a filepath, you use the message object:
      .Attachments.Add objFWD

      if you are using some other message, you need to identify it, assign it to the objFWD object and attach it.

      Reply

  35. Gary says

    Hi Diane,

    When using the macro

    Public Sub CreateNewMessage() Dim objMsg As MailItem
    Set objMsg = Application.CreateItem(olMailItem)

    With objMsg
    .To = "Alias@domain.com"
    .CC= "Alias2@domain.com"
    .BCC = "Alias3@domain.com"
    .Subject = "This is the subject"
    .Categories = "Test"
    .VotingOptions = "Yes;No;Maybe;"
    .BodyFormat = olFormatPlain ' send plain text message .Importance = olImportanceHigh
    .Sensitivity = olConfidential
    .Attachments.Add ("path-to-file.docx")
    ' Calculate a date using DateAdd or enter an explicit date .ExpiryTime = DateAdd("m", 6, Now) '6 months from now .DeferredDeliveryTime = #8/1/2012 6:00:00 PM#

    .Display End With
    Set objMsg = Nothing End Sub

    I keep getting a 'Complie Error: Syntax Error' on the first line:
    Public Sub CreateNewMessage() Dim objMsg As MailItem
    I am using Outlook 2003 and cant seem to understand why it is doing this. Can you help?

    Reply

  36. teodor says

    Diane,

    i am using macro in outlook to send email... macro is running in a constant loop mode ) checking some parameters read from a log file of SCADA system) and sends emails from time to time ( depending on checked parameters) . unfortunately the emails generated are sent only after the macro is stopeed ( code logic works fine) ... seems the macro is putting outlook in " not - responding " mode during code execution . any ideas how to solve it ?

    Reply

    • Diane Poremsky says

      It might be better to pass the process off to a VB utility rather than having outlook constantly monitor. Otherwise, no, I really don't have any good ideas on a solution.

      Reply

  38. Michael says

    Hi Diane,
    I am trying to figure out how to get the date of and email and compare it to today's date. If it matches I will make a copy of the attachment move it to a folder and rename it. If it's older I will move it to a Historical outlook folder. The only thing that I don't have is the date part. I got today's date but how do I get the date of the email? I should mention that I am running this from Excel.

    Reply

    • Diane Poremsky says

      Use RecievedTime or SentOn.
      I use this is a macr0 that checks for aged mail - you could use it to compare today's mail, soothing like this
      intDateDiff = DateDiff("d", objMail.SentOn, Now)
      If intDateDiff = 0 Then
      ' copy attachment
      elseif intDateDiff =>1 Then
      ' archive
      end if

      Reply

  39. Gaurav says

    Yes, the Subject will look some thing like this "App v6.0.0026 is ready for Testing"

    and the body would look like this

    • Mobile Remote: \\2kfp2-eldo\PC-DEV\Setups\App Mobile Remote Setups\v6.0.0026
    • Mobile Standard: \i\2kfp2-eldo\PC-DEV\Setups\App Mobile Standard Setups\v6.0.0026.

    These are the file locations example, where i need to copy manually from the server location to the hard drive every time a new version is released. This process i would like to have it automated.
    Is it possible?

    Reply

  40. Gaurav says

    Thanks Diane,

    In my case i have the subject as "v6.0.x" and the value of "x" keeps incrementing as we get latest build. How do i handle this case ?

    Reply

    • Diane Poremsky says

      Will you get other messages with v6.0. in the subject? If not, you could use that and other conditions for the rule.

      If you need to remember the last number used and look only for the next one, you can increment the value in memory and write it to the registry or a text file for safe keeping so it works after a restart. Check for that value in the subject then decide if it needs processed.

      Reply

  41. Gaurav says

    Hi Diane,

    I am trying to create a Macro in Outlook. I soon as i receive an email from the sender with a fixed subject the macro should scan the body of the email which will have the Source file and it's contents, it should copy the contents of the file to the destination folder i have mentioned in the code.
    Is this feasible. I am getting stuck with the email handler and the copy file operations.
    Any help on this would be appreciated.
    Thanks

    Reply

  42. Bryan says

    Hello, we are trying to get outlook to send an automatic email. Now we have a script but all it does is cause a message box to pop up with the information. Can it be modified to send an email instead?
    Private Sub Application_Reminder(ByVal Item As Object)

    If Item.Class = olTask Then
    If InStr(Item.Subject, "subject") > 0 Then
    ReminderUnreceivedMail
    End If
    End If

    End Sub

    Sub ReminderUnreceivedMail()

    Dim Itms As Items
    Dim srchSender As String
    Dim srchSubject As String

    Set Itms = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
    srchSender = "sender"
    srchSubject = "xyz"

    Set Itms = Itms.Restrict("[SenderName] = 'sender' And [Subject] = 'subject' And [SentOn] > '" & Format(Date, "yyyy-mm-dd") & "'")

    If Itms.Count = 0 Then
    MsgBox "No " & srchSubject & " email on " & Format(Date, "yyyy-mm-dd")
    End If

    Set Itms = Nothing

    End Sub

    Reply

  43. Roberto Mendez says

    Hi Diana. Thanks for all the help and guidance you have been providing here. I kinda feel bad because now I feel I don't know anything about excel, =)
    I have a simple request/question, I hope you can also help.
    I already have a macro that triggers an email based on a employeeid, and its working "fine". But, I have several email accounts configured on my computer, and I would like to know if I can make the macro to "choose" the FROM field as well. Is there some kinda .from function I should use? and where can I put it?

    an example of my macro:

    Sub SendTopSpend_Email()
    Dim ExObj
    Dim olApp As Outlook.Application
    Dim RunTest As VbMsgBoxResult
    Dim i As Integer

    Max = 10
    RunTest = MsgBox("Are you sure you want to Run this and send out many e-Mails? [No for Test]?", vbYesNo, "Send All?")
    If RunTest = vbYes Then Max = 100

    For MSG = 1 To Max
    Dim objMail As Outlook.MailItem
    Dim oAE As Outlook.AddressEntry
    Dim oExUser As Outlook.ExchangeUser
    Dim sso As String, FirstName As String
    Dim DeviceList As String
    Set ExObj = ActiveWorkbook
    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)
    Dim Message As String
    If ActiveCell.Text = "" Then Exit For
    sso = ActiveCell.Text
    objMail.Recipients.Add (sso)
    If objMail.Recipients.ResolveAll Then
    Set oAE = objMail.Recipients.Item(1).AddressEntry
    Set oExUser = oAE.GetExchangeUser
    FirstName = oExUser.FirstName
    LastName = oExUser.LastName

    Message = Message & "Hello " & FirstName & ","

    Message = Message & "Communication is critical to the success of our business but it is also a cost area where each individual manager and employee has a great amount of control. We could use your help to keep costs under control.It is your responsibility to review your detailed billing information by visiting <a href='https://xzy.com."

    DeviceList = "Your spend for October is amongst the top spends (see below)" & _
    "" & ActiveCell.Offset(0, 1).Value & " ($" & Round(ActiveCell.Offset(0, 2).Value, 0) & ") " & ""
    i = 1
    While (ActiveCell.Value = ActiveCell.Offset(i, 0).Value)
    DeviceList = DeviceList & "" & ActiveCell.Offset(i, 1).Value & " ($" & Round(ActiveCell.Offset(i, 2).Value, 0) & ") " & ""
    i = i + 1
    Wend
    Message = Message & DeviceList

    If RunTest = vbYes Then Call SendEmail("xwz.@xwz.com", sso, objMail.Recipients.Item(1).AddressEntry.Manager, Message, "ACTION REQUIRED: Top C&C Spend")
    Call SendEmail("xwz.@xwz.com ", sso, objMail.Recipients.Item(1).AddressEntry.Manager, Message, "ACTION REQUIRED: Top C&C Spend")
    Else
    ActiveCell.Offset(0, 3).Value = " **Not in GAL**"
    ActiveCell.Font.Bold = True
    ActiveCell.Font.Color = RGB(255, 0, 0)
    ActiveCell.Offset(0, 1).Font.Bold = True
    ActiveCell.Offset(0, 1).Font.Color = RGB(255, 0, 0)
    ActiveCell.Offset(0, 3).Font.Bold = True
    ActiveCell.Offset(0, 3).Font.Color = RGB(255, 0, 0)
    End If
    objMail.Recipients.Remove (1)
    ActiveCell.Offset(i, 0).Activate
    Next MSG
    End Sub

    Any help will be greatly appreciated!!
    Regards

    Roberto

    Reply

  44. Alex says

    Hi Diane,

    I'm trying to use a button to email the active workbook in Excel. I want to be able to add additional attachments to the email before it sends. I'd also like to add some predetermined body text to the email and set it to high importance.
    The current macro automatically sends the email:

    Sub Button3_Click()

    Dim emails As Variant

    emails = Array("aturner@domain.com")

    ActiveWorkbook.SendMail Recipients:=emails, Subject:="Process ECN"

    End Sub

    I'm a total VB nubie so any assistance you can offer would be great!

    Reply

    • Diane Poremsky says

      You're working in Excel by the looks of it - so you'll need to use the outlook object model to create the message. There might be better ways to handle to workbook but the outlook part is what you need.

      Sub Button3_Click()
      Dim WB As Workbook, FileName As String
      Dim olApp As Object
      Dim olItem As Object

      Set WB = ActiveWorkbook
      FileName = "email.xls"
      WB.SaveAs FileName:="C:\Users\dianep\Documents\" & FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

      On Error Resume Next
      Set olApp = GetObject(, "Outlook.Application")
      If Err <> 0 Then
      Set olApp = CreateObject("Outlook.Application")
      End If

      Set olItem = olApp.createitem(olmailitem)

      With olItem
      .To = "drcp@cdolive.com"
      .Subject = "Process ECN"
      .body "This is my message"
      .Attachments.Add WB.FullName
      .Display ' .send
      End With

      Set olItem = Nothing
      Set olApp = Nothing

      End Sub

      Reply

  45. Jonathan Lingmann says

    Diane,
    So I want my macro in excel to copy the printable area on the page, open outlook, create new email and copy the data to the body of the new email. Can you help my with this?

    Reply

  46. John Wiesman says

    that seems to put a time stamp in the body...the body itself didn't change but the time stamp changed to 16pt....what am I doing wrong.

    thanks

    Reply

    • Diane Poremsky says

      I thought that was want you wanted with the font size in with the Now function.... if you want the entire body you insert, move the html code - you could use div or p instead of span since you are doing the block

      HTMLBody = "<span style='font-size:16pt;'>" & "Please check and read this document." & Format(Now) & "<\span>"

      Reply

  48. John Wiesman says

    Hi Diane, I am using vba to send single sheets of a workbook to email recipients using outlook. Can I change the font and size of the body?

    End Select
    FilePath = Environ$("temp") & "\"
    FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
    With OutlookMail
    .To = "johnw@dgi.com"
    .CC = ""
    .BCC = ""
    .Subject = "Poinsettia History"
    .body = "Please check and read this document." & Format(Now, .Font.Size = 16)
    .Attachments.Add Wb2.FullName
    .Send
    End With
    Wb2.Close
    Kill FilePath & FileName & xFile
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
    Application.ScreenUpdating = True
    End Sub

    Reply

  49. Alex Matvej says

    Hi, Diane,

    maybe you can help with a little problem we have. Procedure in ms access creates and opens in a loop severel outlook mailitems (emails) with .display method. Now user has let's say 5 emails opened, and now user have to press Send button for each of emails to actually send it.

    Now the problem is, that sometimes not even one email is sent, and sometimes several but not the last email are sent, when a user presses the Send button. I will appreciate much if you could somehow point where to look for the problem? Is it the outlook instance that closing too early, or is there some setting to change, so the email is sent immediately when the Send button is pressed?

    Thank you very much for this article, it helped a lot!!

    Reply

    • Diane Poremsky says

      If outlook is not opened, yes, its because its not open long enough. If you use Exchange online mode, not cached, it will send. (I assume you are using send immediately-that is necessary too.)

      Reply

      • Alex Matvej says

        Thank you for your reply!
        I'm not sure that we use Exchange. We have a POP3 account set up. Should it be with online mode either?
        By send immediately do you mean the Outlook Email sendind setting or some parameter that I should you in VBA code?

      • Diane Poremsky says

        Send immediately in file, options... that needs set. If you aren't using Exchange mailbox and outlook isn't already open on the desktop, the last message will not send - outlook needs more time to make the connection to the server and hand it off.

      • Alex Matvej says

        Thank you so much for this info! Is there a method to keep Outlook instance opened for an hour for example? Or it will keep the VBA program running in background for an hour?

      • Diane Poremsky says

        Offhand, I can't think of any way, short of something like creating an extra message that the user doesn't send. There may be other, more elegant ways of keeping it open.

  50. Tintu Mon says

    Hi Diane,

    I am totally new to VBA, we have a legacy code built by vendor. I wanted to know a statement to avoid sending email for particular scenario. Currently the code sends email no matter what the conditions set.

    Reply

    • Diane Poremsky says

      unless it uses a rule and you can set the conditions in the rule, you need to add an if statement
      If [whatever] then
      'do this
      else
      ' exit or do something else
      end if

      Reply

  51. Prachi Shah says

    Hi Diane,

    It would be great if you can help me out with this.

    I want to create a macro which searches through a particular folder in outlook.

    Folder name and search string would come from particular cells of an excel file and I would prefer to run the macro from excel file itself.

    I created a macro which does as expected and gets search result in excel file.

    But now I want to see these results in the outlook itself. Is there a way we can open outlook from excel vba and enters search string in outlook search area and gets the result there itself?

    Your help will be much appreciated. Many Thanks.

    Prachi

    Reply

  52. Nate says

    Up and running all under the one Macro. Setting to currentItem was where I was getting caught up.... thank you!!!!

    Reply

  53. Nate says

    Diane,
    I would greatly appreciate your input on the following, which is related to the above. I am trying to create a macro that will create a new email with a pre-loaded subject, and an AutoText entry inserted into the body. My goal is to have a single button in Outlook (2003) that I can click and a new message will be generated that wil only require a few minor tweaks before being sent.

    I have tried a couple different approaches, but haven't been able to get everything working:

    This one creates the message and inserts the AutoText perfectly, but I cannot figure out how to add a subject.

    Sub CreateNewMessage1()
    Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=2
    Selection.TypeText Text:="NAME,"
    Selection.TypeParagraph
    Selection.TypeParagraph
    NormalTemplate.AutoTextEntries("email").Insert Where:=Selection.Range, _
    RichText:=True
    End Sub

    This one creates the message with a subject, and inserts the "NAME," field into the body with only the first line of my AutoText entry (which is multiple paragraphs).

    Sub CreateNewMessage2()
    Dim objMsg As MailItem

    Set objMsg = Application.CreateItem(olMailItem)
    With objMsg
    .To = "Alias@domain.com"
    .Subject = "This is the subject"
    .Body = "NAME," & vbNewLine & vbNewLine & NormalTemplate.AutoTextEntries("email")
    .Display
    End With

    Set objMsg = Nothing
    End Sub

    Again, your help is greatly appreciated.
    Thanks,
    Nate

    Reply

    • Diane Poremsky says

      After you create the message you need to identify the message then set the subject and any other fields. Use Set objMsg = Application.ActiveInspector.currentItem rather than Set objMsg = Application.CreateItem(olMailItem).

      BTW, you can do it all in one macro - create the message with the autotext inserted, then identify it using activeinspector then use the with to set the other fields.

      Reply

  54. Emmanuel says

    Diane,
    You are a genius. It has worked perfectly. Thank you.
    I look forward to receiving the macros for inserting Quick Parts in Outlook.
    Very grateful.

    Reply

  55. Emmanuel says

    Thank you Diane.
    Using a macro to insert the quick part will be perfect but I don't know how to create the macro. You help will be appreciated.

    I tried the above code but I am getting "Compile Error: User-defined type not defined" on
    Dim olDocument As Word.Document

    Reply

    • Diane Poremsky says

      Oh, sorry. You need to go to Tools (in the VBA editor), open References. Find Microsoft Word Object Library and add a checkmark.

      I'll work up instructions for quick parts in Outlook, as it will handle larger blocks of text easier.

      In Word I use this:
      Sub InsertQP()
      Dim oTmp As Template
      Dim sPath As String
      enviro = CStr(Environ("USERPROFILE"))
      Set oTmp = Templates(enviro & "\Dropbox\Invoice-template.dotm")
      oTmp.BuildingBlockEntries("1a-12 months").Insert Selection.Range

      End Sub

      Reply

  56. Emmanuel says

    Diane,
    Just like Quick parts, I want to create a button that will insert a specific text in the message body of the email I am replying to.
    E.g. "Your reference for this request is 999"
    I hope you can help.
    Thank you in advance

    Reply

    • Diane Poremsky says

      You can use a macro to insert a quick part or you can use VBA to insert text that is in the macro. Inserting changeable code (999) is more difficult unless your system added the code to the subject. If you plan to type it in, then it's not an issue.

      Select a message and run this macro:
      Sub NewMessageInsert()

      Dim olInspector As Outlook.Inspector
      Dim olDocument As Word.Document
      Dim olSelection As Word.Selection
      Dim strAtt As String

      Dim oItem As MailItem

      Set oItem = Application.ActiveExplorer.Selection.Item(1)

      Set oMail = oItem.Reply
      oMail.Display

      strAtt = "This is my text"

      Set olInspector = Application.ActiveInspector()
      Set olDocument = olInspector.WordEditor
      Set olSelection = olDocument.Application.Selection

      olSelection.InsertBefore strAtt

      Set oMail = Nothing

      End Sub

      Reply

  57. Rajeev says

    Hi Diane,
    You will be able to help me with this, we are going to have an event very soon and all invitations are sent through email, but inorder to find out how many invities are attending i need to insert an email as link with specific information, so that when they click the link a new email message is opened with my email address and space to insert additional, i tried using outlook hyperlink but that will be send information back only with their email address. But i need to get their Full Name, Organization name.

    Regards

    Reply

    • Diane Poremsky says

      You could use a mailto -this is the format: Yes, I'm coming!

      You'll definitely need to compose the mailto using VBA though - something along the lines of the code here. After removing the lines you don't need, you'd use something like
      objMsg.htmlBody = "<a href=" & chr(34) & " rel="nofollow">mailto:me@domain.com?subject=I'll be there&body=" & ocontact.fullname & " - " ocontact.company & chr(34) & ">Yes, I'm coming!</a>"

      This won't prevent them from changing the message in the body though. If you don't have contacts but have the information in a file, this will work, but needs a bit more code.

      Reply

  58. mrrcomp says

    Hi diane
    I have code that creates an email from Access with hebrew text for subject. It works fine except on 1 computer the Hebrew comes out as gibirish? Access 2010 Outlook 2013 windows 7. Any suggestions would be appreciated :)

    Reply

    • Diane Poremsky says

      Sounds like either a problem with Unicode or the font / language on that machine. Is everything configured identically? Same settings in Control panel, language; same fonts installed?

      Reply

  60. stephen says

    Hi Diane, I am trying to create a macro which will open up a new message and uto fill the subject it needs the date to be inserted in the following format yyyymmdd an example is Release-Authorised: 20130908-This_part_is_manualy_added-U so the parts I need to be automatic are: Release-Authorised: yyyymmdd--U hope this makes sense?

    Reply

      • Diane Poremsky says

        I'm not sure which macro you are referring to, but yes, you can add the time. Now, .receivedtime, .senton - any time field in outlook is supported.

Leave a Reply

Your email address will not be published. Required fields are marked *

Mail Tools

Calendar Tools

Productivity

Help & Suggestions

Visit Slipstick Forums.

What's New at Slipstick.com

Home | Outlook User | Exchange Administrator | Office 365 | Outlook.com | Outlook Developer
Outlook for Mac | Common Problems | Utilities & Addins | Tutorials
Outlook & iCloud Issues | Outlook Apps
EMO Archives | About Slipstick | Slipstick Forums
Submit New or Updated Outlook and Exchange Server Utilities

Send comments using our Feedback page
Copyright © 2026 Slipstick Systems. All rights reserved.
Slipstick Systems is not affiliated with Microsoft Corporation.