VBA Help - Saving PDFs with Month/Year then into Outlook

AntonioM00

New Member
Joined
Mar 22, 2019
Messages
1
Hello All - Greetings from Las Vegas, NV, I am new on this message board and am desperately in need of some VBA/Macro Help.

Over the last few months, I have been manually saving 30+ PDFs and then emailing them 30 different times through outlook with a different distribution list for each PDF.

I am trying to automate this process; and I attempted to use the Macro Recorder to no avail.

This is a monthly process and I need a macro that will save the PDF into a folder for the current month; then create an email to its assigned distro list.

Below is what I was able to do with the Macro Recorder; which shows an example of me saving 2 PDFs - however I cannot find a way for me to add the month and year to it without it being a manual process; for instance if I was to run this macro for the month of April it would still say March 2019. This recorded macro also does not involve me sending it to outlook either.

Any help would be appreciated. Thanks!

Sub Distribute()
'
' Distribute Macro
' Distribute functions
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Range("A16").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems( _
"Atlantic City").ShowDetail = True
ActiveWindow.SmallScroll Down:=6
ChDir _
"\\reglvfile2\#lvfile2\HQLRSK\Risk Control\Allocation\2019 Reports\3 March Mid Month\PR"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\reglvfile2\#lvfile2\HQLRSK\Risk Control\Allocation\2019 Reports\3 March Mid Month\PR\BLV March 2019 Mid-Month Allocation -.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Range("A16").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems( _
"Atlantic City").ShowDetail = False
Range("A17").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location").PivotItems( _
"Las Vegas").ShowDetail = True
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\reglvfile2\#lvfile2\HQLRSK\Risk Control\Allocation\2019 Reports\3 March Mid Month\PR\BAC March 2019 Mid-Month Allocation -.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top