Hi David,

Please be specific about the problem.  How was it not successful? 

Did you get a VBA error?  If so, please tell me what it was. A screenshot would be good along with the line of code the debugger stopped at which will be highlighted in yellow.

Did the code run but not do what you expected?

Regards

Phil

Hi David,

This line

.To = WorksheetFunction.VLookup (Range("B2").Value, Worksheets("Staffs").Range("Staffs"), 2)

should be

.To = WorksheetFunction.VLookup (Range("B2").Value, Worksheets("Staffs").Range("Managers"), 2)

the Staffs table does not exist.

To add a company logo to the PDF, just place the image on the Pivot Table sheet and it will be printed into the PDF.

Regards

Phil

Deep thanks Philip, rectify my serious error, had tried again and work perfect,  beautiful and powerful codes, save a lot of our laborious manual work.

To be more greedy, is  there any codes can set in this VB with Password to access and protect the pdf Report from being edit/change.  

as related to confidential data. 

Sorry I test run again with actual data but  after a few record the Macro come to a halt..

Hi David,

Again, need more info please!

Why did it stop?

Did you get an error?  Screenshot?

What line of code is highlighted in the debugger?

Where did you get up to in the list of people?

Have you tried stepping through the code (using F8) to see if you can pinpoint the problem?

https://www.myonlinetraininghub.com/debugging-vba-code

https://www.myonlinetraininghub.com/more-tips-for-debugging-vba

I don't believe there's any way to password protect the PDF when created in Excel.  But the PDF can't be altered unless someone has some PDF editing software, and then they would need to have the password (if it existed) to open the PDF.  So if you had a password protected PDF, and someone has the password, you couldn't prevent them tampering with it anyway.

Regards

Phil

Noted, Philip your advice [ password  issue]

Will tidy-up all Tables, and re-run the Macro  

Will use F8 to debug the VB to locate where the error occurs and screen shot for your further enhance  and assistance.

Thanks again

     On Error GoTo 0
        'On Error Resume Next
                  
        'Create the PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

        'Create a new mail message
        Set OutlookMail = OutlookApp.CreateItem(0)
       
        'Display email and specify To, Subject, etc
        With OutlookMail
       
            .Display
            .To = WorksheetFunction.VLookup(Range("B2").Value, Worksheets("Staffs").Range("Managers"), 2)
            'To = WorksheetFunction.VLookup(Range("B2").Value, Worksheets("Staffs").Range("Staffs"), 2)
            '.CC = Email_CC
            '.BCC = Email_BCC
            .Subject = EmailSubject
            .Attachments.Add PDFFile
               
            ' Change this to True to automatically send emails without first viewing them
            If DisplayEmail = True Then
           
                .Send

       Macro stop at those red code

ok thanks.  I'm guessing it's a VLOOKUP problem, probably because what it's trying to lookup doesn't exist in the table Managers.

How far did the code get?  What was the PT filter set at when the code stopped?  Does that person exist in the Managers table?

Can you manually type a VLOOKUP in the sheet using the value in the PT filter and get a valid result?

How far did the code get?  What was the PT filter set at when the code stopped?  Does that person exist in the Managers table?

David: Very strange Vlookup can map some record but when Vlookup error  occurs, the macro come to a standstill,    

may be the VB statement on error resume GoTo 0

Can you manually type a VLOOKUP in the sheet using the value in the PT filter and get a valid result? David: I tried manually  every single PT filter items but no error found.[ total 46 PT filter record tested]  

Hi David,

David: Very strange Vlookup can map some record but when Vlookup error occurs, the macro come to a standstill,

What was the pivot table filter at this point?  And does that value exist in the Manager table?

may be the VB statement on error resume GoTo 0

Not sure what you mean here but if you are suggesting to turn off error trapping then don't do it, that doesn't fix the problem, it just ignores it.

David: I tried manually  every single PT filter items but no error found.[ total 46 PT filter record tested]  

Then I don't understand why the code stops at the VLOOKUP.  If you can successfully look up every value in the filter then the code should be able to do the same.

Without the actual data causing the issue it's very difficult for me to help you out.

If you can't attach the file here because it has private/sensitive data, you can start a Helpdesk ticket and attach the file there.

https://www.myonlinetraininghub.com/help-desk

Regards

Phil

The code could stop at a vlookup if the workbook or worksheet doesnt exist (i acutally had that happen today). A file open dialog box opens for you to navigate to the file

Did you see anything like that?

Thanks everyone effort and constructive ideas look into the problem.  Fortunately errors resolved as the Email Master List [ System downed file]   - some Eail Email address record contains  a hard carriage return, I think a hidden code, causing so Vlookup function can not complete successful accomplish for the whole list.

I found out only  after using manually Vlookup.

BTW ,

is it possible to add standard text in each mail  in the Macro before sending.

For example :

Dear Staffs,

Good day !

Attached please find this monthly Salary details and payment will be arranged at this month end, please keep for reference and income tax record.

BR

HR Adminsistrator

Hi David,

Glad you figured it out.

To add that message insert these 3 lines after With OutlookMail 

.Body = "Dear Staffs," & vbCrLf & vbCrLf & "Good day !" & vbCrLf & vbCrLf
.Body = .Body & "Attached please find this monthly Salary details and payment will be arranged at this month end, please keep for reference and income tax record."
.Body = .Body & vbCrLf & "BR." & vbCrLf & vbCrLf & "HR Administrator"

Regards

Phil

Thanks Philip, will put on that in the Email Macro.