Excel - A Macro to send mail with attachments

March 2017

Excel is used not only for storing, cleaning and analysing data, but also for automating data-related processes. Automation can be done in Excel by writing or recording macros in VBA. One can use macros to work with programs such as Outlook to send an email with attachments. For this, the user needs to specify the recipient email addresses. While specifying the email addresses, however, one should ensure that the email addresses are not put in quotation marks. Other than this, the user should use the correct syntax to send the attachment without any error.


Issue


I'd like to automatically send an Excel sheet via Outlook.
I use the following macro that I salvaged from another topic.

Sub SendMail()     
' Copy the sheet(1)     
ThisWorkbook.Sheets(1).Copy     
' Send Email     
With ActiveWorkbook     
.SendMail Recipients:=Array("toto@ss.ch", "xxx@sss.ch"), Subject:="Test" & Format(Date, "dd/mmm/yy")     
.Close SaveChanges:=False     
End With 


I have a recipient list that varies according to the sheets, so I modified this macro, keeping in mind that the email recipient is inscribed in cell M1.

Function SendMail()     
Dim y As String     
Dim x As Integer     
y = Sheets(1).Range("M1").Value     
x = Sheets(1).Range("K1").Value     
' Copy the sheet(1)     
ThisWorkbook.Sheets(x).Copy     
' Send Email     
With ActiveWorkbook     
.SendMail Recipients:="y", Subject:="test" & Format(Date, "dd/mmm/yy")     
.Close SaveChanges:=False     
End With     
' Subject:=Range("Feuil1!B1").Value & " " & Range("Feuil1!A1").Value     
End Function     

But it's not working.

Solution


There are some modifications to be made:

y is a variable. So in:
.SendMail Recipients:="y", Subject:="test" & Format(Date, "dd/mmm/yy")  


It should not be put in quotation marks. But must be like this:
.SendMail Recipients:=y, Subject:="test" & Format(Date, "dd/mmm/yy")  

Related


Published by aakai1056.
This document, titled "Excel - A Macro to send mail with attachments ," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).