Hello -
I am currently using the incredible template from https://www.myonlinetraininghub.com/vba-to-create-pdf-from-excel-worksheet-then-email-it-with-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!
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.
