May 14, 2020
Hello -
I am currently using the incredible template from https://www.myonlinetraininghu.....th-outlook to pull a sheet from Excel, convert it to a PDF, and have it attached automatically to an email draft in Outlook.
The recipient of those PDFs has recently asked if they can be sent along in a Word Doc format. I would love to maintain the same process (run the code, select where the file is saved, and then send the email). I am not a VBA savvy person and would love to learn if there is a code written that can support this work order. Thank you!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Patrick,
Here are the changes needed:
Replace ".pdf" with ".docx" in the following line:
PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Name _
& "_" & CurrentMonth & ".docx"
Replace the line that creates the pdf:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=OpenPDFAfterCreating
with this:
Dim WordApp As Object, myDoc As Object
Set WordApp = CreateObject("Word.Application")
Set myDoc = WordApp.documents.Add
WordApp.Visible = True
ActiveSheet.UsedRange.Copy
WordApp.Selection.Paste
WordApp.Selection.Tables(1).AutoFitBehavior (1)
myDoc.SaveAs PDFFile
myDoc.Close
Set myDoc = Nothing
Set WordApp = Nothing
Everything else remains the same, should work.
1 Guest(s)