Send automatic email as PDF attachement from data in excel

[chamseddine_hamdeni]

I have several sheets (sheet1, sheet2; sheet3, etc.).

I want to send the content of these sheets to different email addresses. For example

sheet1 to email1@email.email

sheet2 to email2@email.email

sheet3 to email3@email.email

etc...

Any help ?

[#2 · Accepted solution]

@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.

1. 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.
2.  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 
3. 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

[#3]

@chamseddine_hamdeni 

In this link you will discover different variants of sending e-mails with Excel.

... also sample files to download.

Mail from Excel and make/mail PDF files

none Microsoft site

The third-party products/sites that this article discusses are manufactured by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.

Hope I was able to help you with this info/link.

NikolinoDE

I know I don't know anything (Socrates)

[#4]

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.

[#5]

@NikolinoDE  thank you for the valuable link you send. 

I'm trying to run one of these files. the macros is not running althouh I enabled it from the trust center. can you please check the screenshot and help me ?

[#6]

@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.

[#7]

@Harun24HR 

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

[#8]

@Harun24HR

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:

 

[#9]

@chamseddine_hamdeni Did you enable Programmatic Access?

[#10]

@Harun24HR yes sir I did.

same error with .send  and .display

Your code was working perfectly. This error happened after I modified your code from sending 3 sheets to sending 20 sheets.

After that when I came back to 3 sheets it didn't work also.