export Excel data to existing Word Form & Email

si3po

New Member
Joined
Jan 7, 2019
Messages
45
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,312
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

New Member
Joined
Jan 7, 2019
Messages
45
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,312
You would need to attach a saved file, but that can be deleted afterwards. You might code the macro along the lines of:
Code:
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

New Member
Joined
Jan 7, 2019
Messages
45
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...?
 

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top