• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to send PDFs in Email?



I have managed to find a way of printing all my slicer selections to PDF and store them into a file location using VBA.
Attached is my file.
I also believe there is a way to have a macro that will distribute the email to each advisor as part of the macro. I tried combining an emailing macro with my Print PDF one, but got very stuck.
Does anyone know a way I can combine the two?

Any help would be greatly appreciated.




Is there then a possibility of the pdf selection and email address being selected and "Mail" button pressed in a Slicer loop through? Or is that just dreaming?


Hi @Belleke ... just a quick question on this please?

(Not sure if it's obvious from the code), but I'm trying to get the result from clicking on LB_11 to determine which directory to go to for the PDFs.
LB_11.Column(1) will be a Team Leader's name (eg: "Team Alice"), so what I want is the filepath further down to add that at the end of the main directory.. so that it would try to access X:\Business Performance\Contact Centre\PDFs\Team Alice


Code is as follows (just incase I've missed something obvious out at the top)...

Dim MyFile As String, Bestand As String, MyFolder As String, MyTeam As String
Dim OutApp As Object, OutMail As Object
Private Sub Cmd_00_Click()
    MyFolder = "X:\Business Performance\Contact Centre\PDFs\"  'change path here if needed
    Bestand = MyFolder & L_02.Caption
    Signature = "Signature from Christof" 'change signature here
On Error Resume Next
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .to = L_01.Caption
        .CC = L_11.Caption
        .BCC = ""
        .Subject = T_00.Value
        .Body = T_01.Value & vbNewLine & vbNewLine & Signature & vbNewLine
        .Attachments.Add Bestand
    End With
    MsgBox "Send.", vbInformation, "Done."
End Sub
Private Sub Cmd_01_Click()
LB_00.ListIndex = -1
LB_01.ListIndex = -1
LB_11.ListIndex = -1
L_01.Caption = ""
L_11.Caption = ""
L_02.Caption = ""
T_00.Value = ""
T_01.Value = ""
WB_00.Navigate ("about:blank")
End Sub

Private Sub LB_00_Click()
L_01.Caption = LB_00.Column(2)
End Sub

Private Sub LB_11_Click()
L_11.Caption = LB_11.Column(1)

End Sub

Private Sub LB_01_Click()
L_02.Caption = LB_01.Column(0)
MyFolder = "X:\Business Performance\Contact Centre\PDFs\"  'change path here if needed
WB_00.Navigate MyFolder & L_02.Caption
End Sub

Private Sub UserForm_Initialize()

With LB_00
    .List = [EmailTable].Value
    .ColumnCount = [EmailTable].CurrentRegion.Columns.Count
    .ColumnWidths = "140;0;180 "
End With
With LB_11
    .List = [TLTable].Value
    .ColumnCount = [TLTable].CurrentRegion.Columns.Count
    .ColumnWidths = "0;140;0 "
End With
MyFolder = "X:\Business Performance\Contact Centre\PDFs\" 'change path here if needed
MyTeam = L_11.Caption & "\"
MyFile = Dir(MyFolder & MyTeam & "\*.PDF")
Do While MyFile <> ""
    LB_01.AddItem MyFile
    MyFile = Dir
End Sub
Last edited by a moderator: