Forum

Generate a Word Doc...
 
Notifications
Clear all

Generate a Word Doc .doc from Excel Sheet & Automate email draft

2 Posts
2 Users
0 Reactions
166 Views
(@pmcgetti)
Posts: 6
Active Member
Topic starter
 

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!

 
Posted : 06/06/2020 9:49 am
(@catalinb)
Posts: 1937
Member Admin
 

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.

 
Posted : 07/06/2020 11:32 am
Share: