Sending emails form excel with PDF attachemnt

[annaolczyk]

Hey,

I have a problem. I needs to create Macro that will send emails from excel with the pdf attachment. I tried and search for the code that would help me to start but i am quite green with VBA and i need your help !

The email should be send to selected people ( email addresses in "Send to" ) for different contract. We are having a log of contracts that are being send to vendors and unfortunately not everyone was keeping a track of them so we had to create manual entry log to monitor what is being send and when. I would like to make some automation to make the process a bit easier.

-In column I we have " send to"
-In column J we have "CC address" which is always the same : let say 123@gmail.com
-In column AC we have the subject
-In column AE:AG we have the body
-In column AH we have the location of the PDF document

Would be great if in one of the columns after clicking you have the date when is was send.

I spent hours on trying different codes and trying to personalize them but i am not managing. For some time i was using the hyperlink formula but you cannot attaché pdf documents to that one.


Please help

Thank you

 

[PCosta87]

Hi and welcome to the forum

Maybe something like this:

Sub Send_Email()

    Dim c As Range
    Dim OutLookApp As Object
    Dim OutLookMailItem As Object
   
    For Each c In Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row).Cells
        Set OutLookApp = CreateObject("Outlook.application")
        Set OutLookMailItem = OutLookApp.CreateItem(0)
        With OutLookMailItem
                .To = c.Value
                .CC = c.Offset(, 1).Value
                .Subject = c.Offset(, 20).Value
                .HTMLBody = c.Offset(, 22).Value
                .Attachments.Add c.Offset(, 25).Value
                .Display
    '            .Send
        End With
        Cells(c.Row, 1).Value = Now()
    Next c

End Sub

Notes:

• I assumed you have headers in row 1... code only looks for the emails after row 2.
• For the attachment you only need the string (Path) of the file, complete with extension (simple text will do)
• The body is only using the contents of AE for the moment but you should be able to add the rest as you see fit.

Hope this helps
Let me know if you have any further questions.

 

[Monty]

Hello annaolczyk

Welcome to forum.

Try this, recently answered to our forum member.

http://forum.chandoo.org/threads/export-to-pdf-email-to-multiple-addresses.33256/#post-197441