Excel Macro To Send Email Automatically (3 Suitable Examples)

Trang chủ » Visual Basic Code To Send Email From Excel » Excel Macro To Send Email Automatically (3 Suitable Examples)

Có thể bạn quan tâm

Complete the following steps before applying a macro to send email automatically.

STEPS:

  • From your dataset, go to the Developer tab. Select the option Visual Basic.

3 Suitable Examples of Excel Macro to Send Email Automatically

  • Go to the Tool tab and select the option References.

3 Suitable Examples of Excel Macro to Send Email Automatically

  • A new dialogue box named ‘References – VBAProject’ will open.
  • Check the option ‘Microsoft Office 16.0 Object Library’ and click OK.

Method 1 – Applying Excel VBA Macro to Send Email Automatically Based on Cell Value

STEPS:

  • Right–click on the sheet ‘Based on Cell’.
  • Select the option ‘View Code’.

Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

  • A blank VBA code window will open. Another way to open that code window is to press Alt + F11.
  • Enter the following code in that code window:
Dim rg As Range Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Cells.Count > 1 Then Exit Sub Set rg = Intersect(Range("D6"), Target) If rg Is Nothing Then Exit Sub If IsNumeric(Target.Value) And Target.Value > 400 Then Call send_mail_outlook End If End Sub Sub send_mail_outlook() Dim z As Object Dim y As Object Dim b As String Set z = CreateObject("Outlook.Application") Set y = z.CreateItem(0) b = "Hello!" & vbNewLine & vbNewLine & _ "Hope you are well" & vbNewLine & _ "Visit our site Exceldemy" On Error Resume Next With y .To = "Address" .cc = "" .BCC = "" .Subject = "send mail based on cell value" .Body = b .Display End With On Error GoTo 0 Set y = Nothing Set z = Nothing End Sub
  • Click the Run button or press F5 to run the code.

Apply Excel VBA Macro to Send Email Automatically Based on Cell Value

  • A new dialogue box named Macros will appear.
  • In the Macro Name field, select the macro ‘send_mail_outlook’.
  • Click on the Run button.

  • When the cell value in cell D6 > 400 an email in Outlook will generate automatically with specific recipients. We have to just click on the Send button to send the email.

Read More: Excel Macro: Send Email to an Address in Cell

Method 2 – Sending Email Automatically Based on Due Date with VBA Macro

STEPS:

  • Right-click on the sheet Date.
  • Select the option ‘View Code’.

Automatically Sending Email Based on Due Date with VBA Macro

  • A blank VBA code window opens. We can also press Alt + F11 to get that code window.
  • Enter the following code in the code window:
Public Sub Based_on_Date() Dim aRgDate As Range Dim aRgSend As Range Dim aRgText As Range Dim aRgDone As Range Dim aOutApp As Object Dim aMailItem As Object Dim aLastRow As Long Dim CrLf As String Dim aMailBody As String Dim zRgDateVal As String Dim zRgSendVal As String Dim aMailSubject As String Dim j As Long On Error Resume Next Set aRgDate = Application.InputBox("select the column of due date:", _ "Send Mail Base on Date", , , , , , 8) If aRgDate Is Nothing Then Exit Sub Set aRgSend = Application.InputBox("select the email recipients column:", _ "Send Mail Base on Date", , , , , , 8) If aRgSend Is Nothing Then Exit Sub Set aRgText = Application.InputBox("Select the content column of email:", _ "Send Mail Base on Date", , , , , , 8) If aRgText Is Nothing Then Exit Sub aLastRow = aRgDate.Rows.Count Set aRgDate = aRgDate(1) Set aRgSend = aRgSend(1) Set aRgText = aRgText(1) Set aOutApp = CreateObject("Outlook.Application") For j = 1 To aLastRow zRgDateVal = "" zRgDateVal = aRgDate.Offset(j - 1).Value If zRgDateVal <> "" Then If CDate(zRgDateVal) - Date <= 7 And CDate(zRgDateVal) - Date > 0 Then zRgSendVal = aRgSend.Offset(j - 1).Value aMailSubject = aRgText.Offset(j - 1).Value & " on " & zRgDateVal CrLf = "<br><br>" aMailBody = "<HTML><BODY>" aMailBody = aMailBody & "Hello " & zRgSendVal & CrLf aMailBody = aMailBody & "Message: " & aRgText.Offset(j - 1).Value & CrLf aMailBody = aMailBody & "</BODY></HTML>" Set aMailItem = aOutApp.CreateItem(0) With aMailItem .Subject = aMailSubject .To = zRgSendVal .HTMLBody = aMailBody .Display End With Set aMailItem = Nothing End If End If Next Set aOutApp = Nothing End Sub
  • Use the Run button or the F5 key to run the code.

Automatically Sending Email Based on Due Date with VBA Macro

  • A new dialogue box will pop up.
  • In the input field of that dialogue box, select the due date column range D$5:$D$9.
  • Click on OK.

Automatically Sending Email Based on Due Date with VBA Macro

  • A dialogue box will pop up.
  • In the input field, select the column range B$5:$B$9 that contains the email addresses and click on OK.

  • A window will pop up. Select the message range $C$5:$C$9 in the input field of the pop window.

  • The results are shown in the following image. We get three emails that are automatically created in three different windows of Outlook. This will not create mail for the first two email addresses because the due date of those two projects is over.

Read More: How to Apply Macro to Send Email from Excel with Attachment

Method 3 – Using Excel Macro to Send Email Automatically with Attachments

  • Select the file ‘Attachment.xlsx’’.
  • Click on the option ‘Copy Path’.

Use Excel Macro to Send Email Automatically with Attachments

  • The path of the file that we get:
E:\Exceldemy\Attachment.xlsx

Insert this path in our macro code to send this file by email.

STEPS:

  • Go to the Developer tab and select the option Visual Basic.

Use Excel Macro to Send Email Automatically with Attachments

  • A new window named ‘Project – VBAProject will open.
  • Right-click on the sheet name.
  • Select Insert > Module.

Use Excel Macro to Send Email Automatically with Attachments

  • A blank VBA will open.
  • Enter the following code in that module:
Sub send_Email_complete() Dim MyOutlook As Object Set MyOutlook = CreateObject("Outlook.Application") Dim MyMail As Object Set MyMail = MyOutlook.CreateItem(olMailItem) MyMail.To = "[email protected]" MyMail.cc = "[email protected]" MyMail.BCC = "[email protected]" MyMail.Subject = "Sending Email with VBA." MyMail.Body = "This is a Sample Mail." Attached_File = "E:\Exceldemy\Attachment.xlsx" MyMail.Attachments.Add Attached_File MyMail.send End Sub
  • Hit F5 or click the Run button to run the code.

  • The code will send the attachment to the provided emails in the code. The code sends emails by Outlook.
  • Click the Allow button to let Outlook send the attachment to the given emails.

Read More: Send Email from Excel VBA without Outlook

Download the Practice Workbook

You can download the practice workbook from here.

Send Email Automatically.xlsm

Related Articles

  • Send Reminder Email Automatically from an Excel Worksheet Using VBA
  • VBA to Generate Multiple Lines in Email Body in Excel
  • Macro to Send Email from Excel
  • How to Send Email from Excel with Body Using a Macro
  • Print to PDF and Email Using VBA in Excel
  • How to Use Excel VBA to Paste Range into Email Body
Get FREE Advanced Excel Exercises with Solutions!

Từ khóa » Visual Basic Code To Send Email From Excel