export Excel data to existing Word Form & Email

[si3po]

hi all,

i'm currently in the processes of updating an existing spreadsheet that we insert and track customer demands on.

Previously, we had created a copy of the Word document in Excel and filled the fields with the corresponding data using VLOOKUP to manually match the order number input into a cell at the top and report back the corresponding data in the main excel sheet. This would then be emailed using the 'Select Range & Email' script created by Ron De Bruin.

Circumstances have now changed and we are being mandated by head office to complete a pre-formatted Word document containing a Form, updating it to contain the data for the newly placed demand, then emailing it to multiple recipients. The completed word doc is then deleted as it is no longer required since we can compile an electronic copy again using data on the register if needed.

Herein lies my problem - the new processes mean that we must duplicate the data entry, which is susceptible to mistyping or user error when compiling multiple emails to send out to our suppliers and head office accounts, and can often be very time consuming in itself. I have been asked to try and automate the completion of the Word Form using the existing Excel in order to reduce duplication of work and streamline current processes. If possible, we would like to try and retain a similar process as we currently use - enter the order number, the form fills itself in, click 'Send Email' button on Excel sheet which attaches the document to an email and opens in Outlook for review ready to send.

Can anybody help? My suspicions lead me to using a form of Mail Merge or something similar. Or is there a means of using Excel to overwrite a Word Document held as an Excel sheet?? Any suggestions and assistance to a solution would be greatly received!

 

[Macropod]

If you're able to configure your workbook so that the required output data are always in the same set of cells, you could copy & paste those cells into a Word template, using Paste Special, with the 'paste link' method and the format(s) of your choice. That way, any new document created from the template would automatically have the current Excel data.

Automation could then be done from Excel, to create the new document, unlink the fields, then save & email the document before deleting it. Alternatively, the template could contain a Document_New macro so that, when you create a new document from Word using that template, it unlinks the fields in the newly-created document, then saves & email the document before deleting it.

 

[si3po]

Interesting, thanks macropod!

I'll look at linking the cells across to the existing word document and using the "document.new" scripting to save it off as a separate file for emailing... I wonder if it's possible to make it a temporary file as saving it isn't necessary for long term storage as the data will remain in the Excel sheet to allow replication at a later date if needed.

Would you have any links or know where I could look at the code needed to do the above?

 

[Macropod]

You would need to attach a saved file, but that can be deleted afterwards. You might code the macro along the lines of:

Private Sub Document_New()
' Note: The following code requires a VBA reference to the Outlook Type library,
' set via Tools|References in the VBE
Dim objOutlook As Outlook.Application, objNameSpace As Outlook.NameSpace
Dim objMailItem As Outlook.MailItem, objRecipient As Outlook.Recipient
Dim StrPath As String, StrFlNm As String
StrPath = "C:\Users\" & Environ("UserName") & "\Documents\"
StrName = "Customer Demand " & Format(Now, "YYYYMMDD hhmmss")
With ActiveDocument
  .Fields.Update
  .Fields.Unlink
  ' Save & close the output document
  .SaveAs FileName:=StrPath & StrFlNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
  ' and/or:
  .SaveAs2 FileName:=StrPath & StrFlNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
  .Close SaveChanges:=False
End With
Set objOutlook = New Outlook.Application: Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objMailItem = objOutlook.CreateItem(olMailItem)
objRecipient.Add ("John Wilson")
objRecipient.Type = olTo
With objMailItem
  .Subject = "Customer Demand"
  .Body = "Hi," & vbCr & "Attached is the latest customer demand." & vbCr & "Regards" & vbCr & Environ("UserName")
  .Attachments.Add (StrPath & StrFlNm & ".docx")
  ' and/or:
  .Attachments.Add (StrPath & StrFlNm & ".pdf")
  .Logon , , True
  .Send
End With
' Delete the output document
Kill StrPath & StrFlNm & ".docx"
' and/or:
Kill StrPath & StrFlNm & ".pdf"
Set objRecipient = Nothing: Set objMailItem = Nothing: Set objNameSpace = Nothing: Set objOutlook = Nothing
End Sub

Note that the above code allows the file to be sent as a Word document and/or a pdf.

 

[si3po]

Hi Paul/Macropod,

Just an update to let you know i've solved this using another method.

I've created a 'template' word.doc and have a hidden excel sheet that fills with the data for the word doc to be created based on VLOOKUP formula. It then arranges that data into columns; each column has a header, e.g. <<customer name>> , <<demand ref>> etc.

I then use Excel to open the Word Doc template that contains also the same headers placed where that data needs to go. Then I have Excel tell word to do a find/replace on all of the headers, replacing that header item with the data held on the hidden Excel sheet, looping until all headers are replaced. The document is then saved temporarily, attached to an email to be sent. Word then deletes the file and exits, and the user is free to send the email after checking its all there.

I have the code available if you'd like to see it...?