How to Send an Outlook Email with VBA (Macros)

Here’s an example of a simple macro that sends an Outlook email.

Sub sl()
    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.BodyFormat = olFormatPlain
    objMail.Subject = "Hi buddy"
    objMail.Body = "Whats up" + Chr(13) + Chr(10) + "Greg"
    objMail.To = "goofus@doofus.com"
    objMail.Send
End Sub

8 Responses to “How to Send an Outlook Email with VBA (Macros)”

  1. Jimmy Pena says:

    Nice job, but you forgot to mention what program is doing the automating, i.e. Excel, Word, etc. You would also need to set a reference to the Outlook object library before running this code. You could simplify the code with a With statement, for example:

    With objMail
    .BodyFormat = olFormatPlain
    .Subject = “Hi buddy”
    .Body = “Whats up” & Chr(13) & Chr(10) & “Greg”
    .To = “goofus@doofus.com”
    .Send
    End With

    And don’t forget the security prompt when you invoke the Send method.

    HTH,
    JP

  2. Thanks for the clarification, Jimmy. I was doing the automating in Outlook.

  3. Jimmy Pena says:

    In that case, I take back what I said about the security prompt.

    Thx,
    JP

  4. Wayne Stevens says:

    If I wanted to run the macro everyday via a scheduled task, how would I do that?

  5. Jimmy Pena says:

    Check out the ReminderFire event. It executes right before the reminder is executed. For example, this code would go in the ThisOutlookSession module of Outlook.

    Private WithEvents emailtask As Outlook.Reminders

    Private Sub Application_Startup()
    Dim objApp As Application
    Dim objNS As NameSpace

    Set objNS = Application.GetNamespace(“MAPI”)

    Set emailtask = objNS.GetDefaultFolder(olFolderTasks)

    End Sub

    Private Sub emailtask_ReminderFire(ByVal ReminderObject As Reminder)

    Dim objApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    Set objMail = olApp.CreateItem(olMailItem)
    With objMail
    .BodyFormat = olFormatPlain
    .Subject = “Hi buddy”
    .Body = “Whats up” & Chr(13) & Chr(10) & “Greg”
    .To = “goofus@doofus.com”
    .Send
    End With

    End Sub

  6. john davis says:

    How do I add an email signature to the bottom of this.

    For example:

    John Davis, CPA
    1010 west end.
    New York, NY.

    please help

  7. David Sanders says:

    Does anyone know how to leave a signature at the end of an email using a macro.

    So my body would be “hi, give me a call”

    How in code do I write a signature to the email

    David Sanders
    212 west avenue
    212-555-1212

  8. DJ Retz says:

    May I seek your help too. A macro in Excel constructs and displays the email leaving it for the operator to make final edits and either send, save or abort the email. I want to have the Excel macro wait and then record, on a sheet, which action occurred.

    Regards

    DJ Retz
    Australia