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

Posted by Greg Pinero (Primary Searcher) on Jan 23rd, 2008 and is filed under win32.

Both comments and pings are currently closed.

Add this post to Del.icio.us - Digg

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

  1. Jimmy Pena says:
    January 31st, 2008 at 11:53 am

    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. Greg Pinero (Primary Searcher) says:
    January 31st, 2008 at 11:59 am

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

  3. Jimmy Pena says:
    January 31st, 2008 at 3:53 pm

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

    Thx,
    JP

  4. Wayne Stevens says:
    February 5th, 2008 at 2:53 pm

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

  5. Jimmy Pena says:
    February 5th, 2008 at 7:31 pm

    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:
    March 5th, 2008 at 8:15 am

    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:
    March 6th, 2008 at 8:09 am

    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:
    April 24th, 2008 at 12:38 am

    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