Excel Macro to Import Data Into Word Mail Merge Document

[djboyag]

Hi,

I am new to programming and trying to come up with a script to complete a process at work. Basically this is a 2 day process. On the first day, we are importing a spreadsheet from our share point site and formatting the sheet to be emailed to an external service to have accounts activated. The workbook is then saved to a network location to be used for day 2. I have already created a code to complete these step:

Sub HUP_Day_1()

'This script will take automate the Day 1 process of the HUP Program


Dim r As Long
Dim oApp, oMail As Object, _
tWB, cWB As Workbook, _
FileName, FilePath As String

Application.ScreenUpdating = False

'This process deletes all rows with a TRUE value in the "DR Sent?" column

For r = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 To ActiveSheet.UsedRange.Row Step -1
If Cells(r, 7) = True _
Then Cells(r, 1).EntireRow.Delete
Next r

'Deletes unnecessary columns'

Columns("E:I").Select
Selection.Delete Shift:=xlToLeft

'Set email id here, it may be a range in case you have email id on your worksheet

'Mailid is the TO field

'ccMailid is the CC field

Mailid = "email@address.com"

ccMailid = ""

'This process writes the spreadsheet to the HUP directory with the file name "HUPMMDDYY.xlsx"

ChDir "\\bnas01\tek$\HUP"
ActiveWorkbook.SaveAs FileName:="\\bnas01\tek$\HUP\" & "HUP" & Format(Date, "MM") & Format(Date, "DD") & Format(Date, "YY"), _
FileFormat:=51, CreateBackup:=False


'Write your email message body here , add more lines using & vbLf _ at the end of each line

Body = "Program Code: 0BC4A1E" & vbLf _
& "CNL#: CNL107921" & vbLf _
& vbLf _
& "Please whitelist the individuals in the attached spreadsheet. Please let us know if there are any questions or concerns." & vbLf _
& vbLf _
& "Regards," & vbLf _
& vbLf _
& "Name"


'Copy Active Sheet and save it to a temporary file

Set cWB = ActiveWorkbook
ActiveSheet.Copy

Set tWB = ActiveWorkbook
FileName = "HUP" & Format(Date, "MM") & Format(Date, "DD") & Format(Date, "YY") & ".xls" 'You can define the name
FilePath = Environ("TEMP")

On Error Resume Next
Kill FilePath & "\" & FileName
On Error GoTo 0
Application.DisplayAlerts = False
tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
Application.DisplayAlerts = True

'Sending email through outlook

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.SentOnBehalfOfName = "email@address.com"
.To = Mailid
.cc = ccMailid
.Subject = "Whitelist for Program Code 0BC4A1E CNL# CNL57921"
.Body = Body
.Attachments.Add tWB.FullName
.send
End With

'Delete the temporary file and restore screen updating

tWB.ChangeFileAccess Mode:=xlReadOnly
Kill tWB.FullName
tWB.Close SaveChanges:=False
cWB.Activate
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

End Sub

On day 2, I need to come up with a script to pull the data from the excel document saved on day 1 into a word document that already has the merge fields. The easiest way I was thinking to do this, was to create a macro in Excel to call Word and enter the merge data.

I was also thinking of creating a script in Word to pull the prior days excel file from the network share. The problem with this is that we are not running the scripts on the weekends and holidays. So we would have to pull from the latest date in the folder. If anyone has any insight on how to create this script, some help would be greatly appreciated!