Forum Discussion
Send automatic email as PDF attachement from data in excel
- Aug 19, 2022
chamseddine_hamdeni You can try below codes to send individual sheets to separate emails. To understand codes better, I have divided it into 3 parts.
- Generate PDF files to insert to email as attachment. You can give sheet names to Split("Sheet1|Sheet2|Sheet3", "|") this array which you want to generate PDF files.
- Send Emails. This will iterate each cells from A2 to downward and send emails to each cell value with attachment from this Split("Sheet1|Sheet2|Sheet3", "|") list of sheets. In this part of codes you can use .Send instead of .Display to send mail directly without displaying it. Testing purpose I have used .Display so that we can check it is generating emails correctly. To Send mails directly without any warning to must enable Programmatic Access to Outlook. Read below articles to enable it. Link 1: slipstick.com , Link 2 , Microsoft Documentation
- Delete PDFs after sending mails (If anyone wish). Sample file is attached herewith.
Option Explicit 'Generate PDFs for each listed sheet. Sub GeneratePDFs() Dim strID As String Dim shts As Variant, i As Integer On Error Resume Next MkDir ThisWorkbook.Path & "\PDF_Files" Err.Clear strID = Sheets("SendMail").Range("A2").End(xlDown).Address shts = Split("Sheet1|Sheet2|Sheet3", "|") 'Declaring sheet names For i = LBound(shts) To UBound(shts) Sheets(shts(i)).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ ThisWorkbook.Path & "\PDF_Files\" & shts(i) & ".pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, _ OpenAfterPublish:=False Next MsgBox "PDF file creation finished successfully.", vbInformation, "PDF Creation" End Sub 'Send mail to individual each pdf as separate mail. Public Sub SendMail() Dim strbody As String Dim sendTo As String Dim emailCell As Range Dim OutApp As Object Dim OutMail As Object Dim SalaryMonth, strMailRange As String Dim shts As Variant, i As Integer strMailRange = Sheets("SendMail").Range("$A$2").End(xlDown).Address strbody = "Please check the attached file." shts = Split("Sheet1|Sheet2|Sheet3", "|") 'Declaring sheet names i = 0 For Each emailCell In Sheets("SendMail").Range("$A$2", strMailRange) Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .Attachments.Add ThisWorkbook.Path & "\PDF_Files\" & shts(i) & ".pdf" .to = emailCell .Subject = "Sending Mail As PDF" .HTMLBody = strbody .Display '.Send 'Uncomment this line to send mail directly without displaying it. End With i = i + 1 Next emailCell MsgBox "Mail sending completed successfully.", vbInformation, "Mail Sending" End Sub 'Delete PDFs after sending via mail. Public Sub DelPDFs() On Error Resume Next Kill ThisWorkbook.Path & "\PDF_Files\*.*" 'RmDir ThisWorkbook.Path & "\PDF_Files\" MsgBox "All PDF files are deleted.", vbInformation, "Delete PDFs" End Sub
Thank you Harun24HR so much for your help... it is so much appreciated.
The PDF are successfully generated (although I don't know where are they stored, but no problem).
However, do I have to send them manually ?
please check the screenshot attached.
chamseddine_hamdeni PDFs are stored in same folder where you keep the excel file (you can keep it to any location of hard disk or desktop or document folder). In vba code just delete .Display and write .Send, read codes comments carefully. Also if it helps then tick mark the answer.
- chamseddine_hamdeniAug 19, 2022Copper Contributor
I have no enough words to thank you. Your code is working from the begining but It was my mistake I didn't read the comment.
You saved my life thank you so much. ALLAH YBARIKLIK
- chamseddine_hamdeniAug 20, 2022Copper Contributor
My friend, the VBA codes related to PDF are working perfectly. But when I click send emails I face the following problem:
Microsoft Excel is waiting for another application to complete an OLE action
It is not related with your code but with my settings... can you help me ?
this is a screenshot of my settings:
- Harun24HRAug 21, 2022Bronze Contributor