Likes Likes:  0
Results 1 to 5 of 5

Thread: export Excel data to existing Word Form & Email

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default export Excel data to existing Word Form & Email

    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!

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,218
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: export Excel data to existing Word Form & Email

    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export Excel data to existing Word Form & Email

    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?

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,218
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: export Excel data to existing Word Form & Email

    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.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Member
    Join Date
    Jan 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: export Excel data to existing Word Form & Email

    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...?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •