• 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

    Hui...

  • 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?

Christof

Member
Hi,

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.

Thanks
Christof
 

Attachments

  • PDF and Send Example.xlsm
    257.7 KB · Views: 10
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

64798

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

Code:
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
        .Send
    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
Loop
End Sub
 
Last edited by a moderator:
Back
Top