Macro to run a batch, selecting from dropdown list and creating PDF and email

[Slongy01]

Hi everyone, thank you so so much for taking the time to read this

I have to prepare 660 statements which I've created in Excel. I have a Worksheet which has the layout sorted, with a dropdown list for each individual's name. I've managed to create a Macro that cycles through each drop down and creates a PDF of the right area of the Worksheet and saves them in a folder with a name and date (so far so good!)

But, I actually need to send them out to the individuals, so I'd like to be able to add to it, so rather than putting them into a folder, it generates an email in outlook with the PDF attached.. I have the email address in the same workbook, so could potentially bring this in. I would like to be able to create a standard text as well that goes into the Body and also the title of the email.

This is my current Macro:

Sub Test2()
Const NamedRangeName = "AM"
Const SheetName = "Statement"
Const CellWithDropdown = "B5"
Const PrintRange = "A1:L21"
Const DefaultFolder = "C:\Test\PDF"
Dim fPath As String, choice As Range, fName As String
'allow user to select folder
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select destination folder for PDF export"
.InitialFileName = DefaultFolder
If .Show = -1 Then fPath = .SelectedItems(1)
End With

'rest of code
If fPath <> "" Then
With Sheets(SheetName)
For Each choice In Range(NamedRangeName)
.Range(CellWithDropdown) = choice
fName = choice & Format(Date, " ddmmyy") & ".pdf"
.Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & "\" & fName
Next
End With
Else
MsgBox "No folder selected"
End If

End Sub


if anyone could help me to change the output from looking for a folder, to a populated email with body text, title and address it would be life changing!

Many thanks to anyone who takes the time to look

Slongy

 

[John_w]

Welcome to MrExcel forums.

You still need to save (temporarily) each PDF in order to attach it to an email.

Try this macro, changing the email address to reference the cell containing the email address:

Sub Test2a()
    Const NamedRangeName = "AM"
    Const SheetName = "Statement"
    Const CellWithDropdown = "B5"
    Const PrintRange = "A1:L21"
    Const DefaultFolder = "C:\Test\PDF"
    Dim fPath As String, choice As Range, fName As String
    
    fPath = "C:\Test\PDF\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    With Sheets(SheetName)
        For Each choice In Range(NamedRangeName)
            .Range(CellWithDropdown) = choice
            fName = fPath & choice & Format(Date, " ddmmyy") & ".pdf"
            .Range(PrintRange).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName
            Send_Email fName, "email.address@domain.com"
            Kill fName
        Next
    End With
    
End Sub


Private Sub Send_Email(PDFfullName As String, SendToEmailAddress As String)

    Static oApp As Object
    Dim oMail As Object
    
    If oApp Is Nothing Then Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.createItem(0)
   
    With oMail
        .To = SendToEmailAddress
        .Subject = "Standard email subject"
        .Body = "Standard email body text"
        .Attachments.Add PDFfullName
        .Send 'or .Display
    End With
       
End Sub

PS - please use the <vba/> tag.