How to use automation to send ms outlook mail using Excel VBA

Hi Dr. Takyar,
I would like to thank you for your insightful trainings you have available. I would like your assistance in writing this macro for a user form I’ve created.
1).What would be the macro code to receive an email notification once the user clicks the submit button?
2).How to make certain fields required fields?
3).How can I make the captured data not visible to the user?
Thanks in advance!
Brittane
The 2nd and 3rd questions are answered here:
Ensure data is entered
How to hide rows
Protecting Worksheets Using VBA

The complete code is given below:
Option Explicit
Sub SendMail(Optional AttachmentPath)
Dim appOutlook As Outlook.Application
Dim mitOutlookMsg As Outlook.MailItem
Dim recOutlookRecip As Outlook.Recipient
Dim attOutlookAttach As Outlook.Attachment
‘ Step 1: Initialize an Outlook session.
Set appOutlook = CreateObject(“Outlook.Application”)
‘ Step 2: Create a new message.
Set mitOutlookMsg = appOutlook.CreateItem(olMailItem)
With mitOutlookMsg
‘ Step3: Add the To recipient(s) to message.
Set recOutlookRecip = .Recipients.Add(“Harry Mitchell”)
recOutlookRecip.Type = olTo
‘ Add the CC recipient(s) to the message.
Set recOutlookRecip = .Recipients.Add(“Laura Jones”)
recOutlookRecip.Type = olCC
‘ Set valid properties like Subject, Body, and Importance of the message.
.Subject = “Email Automation with MS Outlook”
.Body = “Testing…” & vbCrLf & vbCrLf
.Importance = olImportanceHigh ‘High importance
‘ Add attachments
If Not IsMissing(AttachmentPath) Then
Set attOutlookAttach = .Attachments.Add(AttachmentPath)
End If
‘ Resolve every Recipient’s name
For Each recOutlookRecip In .Recipients
recOutlookRecip.Resolve
If Not recOutlookRecip.Resolve Then
mitOutlookMsg.Display
End If
Next
.Send
End With
Set mitOutlookMsg = Nothing
Set appOutlook = Nothing
End Sub
Private Sub CommandButton1_Click()
SendMail
End Sub
Private Sub CommandButton2_Click()
Dim eRow As Long
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
If TextBox1.Text = “” Then
MsgBox “Name cannot be blank”
End If
Cells(eRow, 1) = TextBox1.Text
End Sub
Private Sub CommandButton3_Click()
End
End Sub
Private Sub CommandButton4_Click()
TextBox1.Text = “”
TextBox1.SetFocus
End Sub
Private Sub CommandButton5_Click()
ActiveWorkbook.Save
End Sub
Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub

Watch the training video below to see how the process of complete automation of sending an email message with attachment through MS Outlook using VBA is implemented:


Further reading:
code to open outlook and attach document to send

8 thoughts on “How to use automation to send ms outlook mail using Excel VBA

  1. Hema

    Dear Dinesh Sir,

    Thank you for all the knowledge sharing and the inputs from you.

    I have a request hope this wil be addressed by you, I wanted to know if there is way where we can automate Mail Merge and save the output in a defined file in PDF format.

    So using the data from excel we have to auto mail merge to word document and then give a code where the output is saved in a defined folder in PDF format.

    Request your help here and thank you once again for everything.

    Hope to hear from you shortly.

    Regards,
    Hema

    Reply
  2. Srikant singh

    Dear Dinesh Sir

    as per your youtube video sending mail vai gmai i am doing same thing but i am not get the on my gmail account show plese help me

    Sub sent_mail_vai_gamil()

    Dim MYMAIL As CDO.Message

    Set MYMAIL = New CDO.Message

    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smptauthenticate”) = 1
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “srikant91.mis@gmail.com”
    MYMAIL.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “9971601228”

    MYMAIL.Configuration.Fields.Update

    With MYMAIL
    .Subject = “This is a test Mail”
    .From = “srikant91.mis@gmail.com”
    .To = “srikant91.mis@gmail.com”
    .TextBody = “good morning!”
    End With
    On Error Resume Next
    MYMAIL.Send
    ‘MsgBox (“your Mail has been sent”)
    Set MYMAIL = Nothing

    End Sub

    Reply
  3. love

    Hi thanks for the great code but wanted to ask one question that …

    i saved few draft in outlook through this macro and wanted to open attachment and paste picture screen shot in mail body can you help me with the code…..?

    Reply
  4. mohamed chihime

    hi Dr Takyar,

    Thanks for your kind advice, and VBA teaching code.
    I would have I have excel sheet with many email emails, I would like to build VBA to send to each one in this sheet an email with a text shows each one balance due/ or confirmation letter take the balance shown in column C and put it in its place and send directly to all clients

    Reply
  5. ibrahim

    Hello there,
    I use Microsoft Outlook 2010.
    My wish is that,
    mail from a specific excel file to a specific folder you’d get collected simultaneously.
    There’s a macro for my hand,
    But
    Only conveys the INBOX folder.
    Can you fix this

    Reply
  6. Suranjan Chakraborty

    Hello Dr. Katyar

    With your help have been able to create a macros for sending out reminder mails on dates specified. Used the below link https://www.youtube.com/watch?v=x2_y0_KDaN0. Now would like to attach some images in this mail body, and change the font color / size for the mailer to be sent.

    Please advise

    Thanks
    Suranjan

    Reply
  7. Deepak Sirsale

    Hello Sir,

    Thanks for uploading such a wonderful information for new bees in VBA like me. I have a question, I have created a code in Excel to send mails. What I want to know is, I want to update the status of mail if it is been sent by user or not. It user close (or press the close button) by mistake, my excel cell should be updated as “mail sent” and vice-o-versa. I want to update the status in cell “D1” Please help…! below is my coding.

    Sub test_mail()
    Dim outapp As Outlook.Application
    Dim mymail As Outlook.MailItem

    Set outapp = New Outlook.Application
    Set mymail = outapp.CreateItem(olMailItem)

    With mymail
    .To = Sheets(“sheet1”).Range(“b1”).Value
    .CC = Sheets(“sheet1”).Range(“b2”).Value
    .Subject = Sheets(“sheet1”).Range(“b3”).Value
    .Body = Sheets(“sheet1”).Range(“b4”).Value & vbNewLine & vbNewLine & Sheets(“sheet1”).Range(“b5”).Value
    .Attachments.Add Environ(“userprofile”) & “\desktop\mail.txt”
    .Display
    End With
    End Sub

    Reply

Leave a Reply

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