Excel - A Macro to send mail with attachments

November 2016

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 :

This document entitled « Excel - A Macro to send mail with attachments  » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.