VBA to send PDFs via Outlook not working

[DME]

Hi everyone,

(I've posted a sample file in the post below this one)

I modified code that I found online a few years ago for a "PDF Generator" (I don't remember the author, so unable to give him/her credit...but this isn't my code) and now need to revisit it for a work project.

Can anyone help me understand why the "Send Manager Emails" button isn't working? The "Send Test Emails" button seems to work just fine but I can't figure out what's wrong with the code.

Thanks in advance for your thoughts and suggestions!

 

[DME]

Found a way to attach a sample file. Please see attached.

 

[Monty]

Hello Dme

Thanks for sharing!

 

[Logit]

I commented out all of your Error checking and Error Handling sections. This then highlighted this portion of your code as offending:

wsR.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strSavePath & strPDFName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

The error message indicates the macro is unable to create/save the PDF file.

 

[DME]

Hi @Logit , thank you so much for your review and for isolating the error in the code. Much appreciated.

Does anyone have any thoughts on how to fix this code? I'm still struggling with the fact that this is working for the test emails but not for the actual (i.e., manager) emails. Any additional insights would be greatly appreciated.

Thanks!

 

[Logit]

I reviewed the project again. The attached works as desired. All fields on the "Email Settings" sheet must be filled in before trying to send any emails (Test or Mgr). If the fields are not completed first, the Mgr emails will error out and the program fails. This was the design of the project creator.

I uncommented all of the error checking. The project should be ready to go now.

The only other thing of note, OUTLOOK must be open and running before sending emails. This project checks to see if OUTLOOK is operational. If it isn't, the program errors out.

 

[DME]

Great catch with the "Email Settings" tab.

It's working better than before but when I "Send Manager Emails" - 3 emails are created in Outlook with the attachment and the body of the email as defined. The "To" field though is pulling from the InfoA field (Column E). It should pull from To Email (Column J) and also send the emails automatically.

The strSendTo is equal to the named range "rngSendTo" but that only appears to store the test email address from the "Email Settings" tab...which is why I'm assuming the test emails are working properly.

@Logit did it work correctly (i.e. auto send) when you tried?

Thanks again for all of your time and attention on this question.

 

[Logit]

Tested all methods. It works as it should now.

If you want the emails to send automatically, uncomment the .Send line and comment out the .Display line.

If you want to view the emails before sending, do the opposite.

 

[DME]

Perfect - @Logit thank you again for everything!

 

[Logit]

You are welcome.