The VBA code makes it possible to send emails, the simplest way to do this is with the help of Outlook.
As seen in by performing the procedure in the Early Binding format., it is first necessary to enable the reference library
Remember that changing the code for Late Binding allows for greater backward versions compatibility.
Be sure to set up an account in Outlook before you run the macros.
Choose between the.
Code for Early Binding
In this case enable the as explained above.
Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutApp = New Outlook.Application 'Object for Outlook Set OutMail = OutApp.CreateItem(olMailItem) 'Object for mail within Outlook
Code for Late Binding
Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") 'Object for Outlook Set OutMail = OutApp.CreateItem(0) 'Object for mail within Outlook
We associate values to some properties of the OutMail object for the email construction.
With OutMail .body = "Hello," & vbNewLine & "This is an example email" .Subject = "Example of subject" .To = "RECIPIENT_EXAMPLE@EMAIL.COM" 'Replace the email of this line .Display 'Method to show content End With
The .Display method assists in constructing the code, since it leaves the email visible for validation, although it is not necessary for the code to work.
If everything is as planned just send it to the recipient:
OutMail.Send 'Send email
As best practices, disassociate objects with keyword Nothing to clear them from memory.
Set OutMail = Nothing Set OutApp = Nothing
We will then have the following process:
Sub simpleEmail() 'Using Late Binding Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") 'Objeto para Outlook Set OutMail = OutApp.CreateItem(0) 'Objeto para email dentro do Outlook With OutMail .body = "Hello," & vbNewLine & "This is an example email" .Subject = "Example of subject" .To = "RECIPIENT_EXAMPLE@EMAIL.COM" 'Replace the email of this line .Display 'Method to show content End With OutMail.Send 'Send email Set OutMail = Nothing Set OutApp = Nothing End Sub
We can build a macro to send an email with an attached report and its preview as an image in the body of the message.
For this exercise we will have two Workbooks: SalesReport.xlsx (the report) and SendingEmail.xlsm (file with the macro).
Preview of SalesReport.xlsx
Within a module in SendingEmail.xlsm we can add the following code:
Sub sendEmail() 'Declaration of variables Dim i as integer Dim WBk As New Workbook Dim reportSheet As Worksheet Dim Rng As Range Dim ReportFile As String Dim OutApp As Object Dim OutMail As Object 'Set the report path ReportFile = "C:\...\SalesReport.xlsx" 'Replace with the path where SalesReport.xlsx is Set WBk = Workbooks.Open(Filename:=ReportFile, ReadOnly:=False, Notify:=False) ReportName = Right(ReportFile, (Len(ReportFile) - InStrRev(ReportFile, "\"))) Set WBk = Workbooks(ReportName) 'The above code is equivalent to: Set WBk = Workbooks("SalesReport.xlsx") Set reportSheet = WBk.Worksheets(1) 'Report tab from where an image will be generated 'Range at which the image will be generated Set Rng = reportSheet.Range("A1:E6") 'A tab called "Temp" will be created to temporarily store the messages of the body of the email Worksheets.Add After:=WBk.Sheets(WBk.Sheets.Count) ActiveSheet.Name = "Temp" 'Email Body Text Cells(1, 1) = "Dear all," Cells(2, 1) = "Below is the preview of the results report." 'We will use the WordEditor property to paste into the body of the email 'It is important to load Outlook so that the WordEditor property works properly Shell ("C:\Program Files (x86)\Microsoft Office\root\Office16\OUTLOOK.EXE") Application.Wait (Now + TimeValue("0:00:05")) 'Wait for Outlook to open 'Early Binding Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .to = "RECEIVER@EMAIL.COM" 'Enter the recipients as String .CC = "COPIED1@EMAIL.COM; COPIED2@EMAIL.COM" 'Here are copied, also as String .Subject = "Results report" 'Email subject .Display End With Set wEditor = OutApp.ActiveInspector.WordEditor 'Using WordEditor to paste elements in the body of the email OutApp.ActiveWindow.Activate 'Enable email window for pasting For i = 1 To 2 Cells(i, 1).Select Cells(i, 1).Copy wEditor.Application.Selection.PasteSpecial xlPasteValues Next i reportSheet.Activate reportSheet.Range(Rng.Address).Select Selection.CopyPicture xlScreen, xlPicture 'Copy as image wEditor.Application.Selection.Paste 'Anexando o arquivo SalesReport.xlsx no email With OutMail .Attachments.Add (ReportFile) End With Application.DisplayAlerts = False 'Disable the message alert for non-stop macro Worksheets("Temp").Delete 'Removal of the tab created only to contain the text that will be sent Application.DisplayAlerts = True 'Run up to here, if you do not want to send the email OutMail.Send 'Enviar o email 'Disassociate variables Set WBk = Nothing Set reportSheet = Nothing Set OutApp = Nothing Set OutMail = Nothing Set wEditor = Nothing End Sub
Note that in this example the WordEditor property was used to perform collage-related tasks in the body of the email, unlike the simple example previously demonstrated.
WordEditor is a way to paste images together with text.
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2021 SuperExcelVBA | ABOUT