Sending email via excel, with conditional body based on delivery location and attaching pdf.

[Gircu000]

Hi Everyone,

I am seeking help to create a macro to sends emails along with pdfs.I will save the filename as values in a2,a3 and so on in a folder on the desktop.
basically these are purchase orders which would get delivered based on the product storage location( i have 3 storage locations)
my cells are setup in the following manner.
Incase column FO has the words "PICK UP" OR "PICKUP", then the body should read as " Hi, Please send us pick up details"(with some line breaks)
the emails are cc'd to one person always.

 

[John_w]

See if this gets you started. You need to edit the code to change the PDFsFolder string to the folder on your Desktop. As written, the emails are saved in the Outlook Drafts folder, allowing you to check them; simply replace the .Save line with the .Send line to send the emails.

Public Sub Send_Emails()

    Dim OutlookApp As Object 'Outlook.Application
    Dim OutEmail As Object 'Outlook.MailItem
    Dim ws As Worksheet, r As Long
    Dim PDFsFolder As String
    
    PDFsFolder = "C:\path\to\Desktop\subfolder\"   'CHANGE THIS
    
    If Right(PDFsFolder, 1) <> "\" Then PDFsFolder = PDFsFolder & "\"
    
    Set OutlookApp = CreateObject("Outlook.Application") 'New Outlook.Application
    
    Set ws = ActiveSheet
    
    For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        Set OutEmail = OutlookApp.CreateItem(0)
        With OutEmail
            .To = ws.Cells(r, "D").Value
            .CC = ws.Cells(r, "G").Value
            .Subject = "Subject here"
            .Body = ws.Cells(r, "H").Value
            .Attachments.Add PDFsFolder & ws.Cells(r, "A").Value & ".pdf"
            .Save  'save email in Drafts
            '.Send 'or send email
        End With
    Next
    
    Set OutlookApp = Nothing
    
End Sub

 

[Gircu000]

See if this gets you started. You need to edit the code to change the PDFsFolder string to the folder on your Desktop. As written, the emails are saved in the Outlook Drafts folder, allowing you to check them; simply replace the .Save line with the .Send line to send the emails.

Public Sub Send_Emails()

    Dim OutlookApp As Object 'Outlook.Application
    Dim OutEmail As Object 'Outlook.MailItem
    Dim ws As Worksheet, r As Long
    Dim PDFsFolder As String
   
    PDFsFolder = "C:\path\to\Desktop\subfolder\"   'CHANGE THIS
   
    If Right(PDFsFolder, 1) <> "\" Then PDFsFolder = PDFsFolder & "\"
   
    Set OutlookApp = CreateObject("Outlook.Application") 'New Outlook.Application
   
    Set ws = ActiveSheet
   
    For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        Set OutEmail = OutlookApp.CreateItem(0)
        With OutEmail
            .To = ws.Cells(r, "D").Value
            .CC = ws.Cells(r, "G").Value
            .Subject = "Subject here"
            .Body = ws.Cells(r, "H").Value
            .Attachments.Add PDFsFolder & ws.Cells(r, "A").Value & ".pdf"
            .Save  'save email in Drafts
            '.Send 'or send email
        End With
    Next
   
    Set OutlookApp = Nothing
   
End Sub

Hi,

Thank you for your quick response. i tried to tweak the above code but received some debug errors on this line.

.Attachments.Add PDFsFolder & ws.Cells(r, "A").Value & ".pdf"

and then this

For r = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

I created a folder on the desktop as the same name and placed a pdf in the folder .

i tried to change the reference to A3 but had the errors

.Attachments.Add PDFsFolder & ws.Cells(r, "A3").Value & ".pdf"

 

[John_w]

I created a folder on the desktop as the same name and placed a pdf in the folder .

I don't understand the highlighted bit. What you do mean by 'as the same name'?

I will save the filename as values in a2,a3 and so on in a folder on the desktop.

Using your cell A2 value ("1234") as an example, the code expects the file "1234.pdf" to exist in the specified subfolder on your Desktop, which in the code is defined as "C:\path\to\Desktop\subfolder\" (you have to change this string to the actual subfolder on your Desktop). And all the PDF files named in column A must exist in the same Desktop subfolder.