Using custom named Word docs to build Email bodies using Excel VBA

[RedMonkey]

I have gone through the forums and found bits an pieces but nothing definitive regarding my personal quest.
Over time I have build a pretty flexible automatic email program that works off multiple criteria and build/customizes and email before sending.

Obviously if an end user wants the email changed I need to go in and changes things in the code, not complicated but it keeps the end user waiting for me to get around to it.

The code below is a common adaptation of my basic template, the items in red are not used in this process.

So my question is: Is it possible that INSTEAD of having my program build the email with code, have it pull a specifically named word.doc and use it for the email body??

Now I have already gone through Ron's Excel tips as well (great resource when I built the original templates) but it does not hit the button on my question above. I'm pretty comfortable using macros that create custom named files that then are referenced as automatic attachments and all sort of custom names files being used in automatic process.
I've found automailers, ways to auto create email from word docs and all sorts of other processes but nothing I can seem to use to have excel pull a worddoc name and use it for an email body.

This will allow the VBA code to continue using pulled data to customize the beginning of a email (the end users are used to changing the basic info monthly that generates these emails) but the end user could also change the email body and signature with no problems at all.


In the end any straight hints would be fine since I have learned alot from not having people do my homework so to speak.

Sub ConcurEmail()
'Working in Office 2000-2010
'Process to send out emails regarding application problems based on issue.
'Email Body Variables
    Dim empHTML As String
    Dim UnassHTML As String
    Dim NotsubHTML As String
    Dim mealHTML As String
    Dim dupHTML As String
    Dim pastHTML As String
    Dim futureHTML As String
'Email Variables
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
'email body variable tests
    If ActiveCell.Offset(0, 2).Value > "0" Then
      empHTML = empHTML & ActiveCell.Offset(0, 2)
End If
    If ActiveCell.Offset(0, 4).Value = "y" Then
      UnassHTML = UnassHTML & "Please be advised that you have not completed an expense report in BLANK for travel card transactions related to your February statement. You must complete an expense report for these transactions by 3/16/12 and submit it for approval to your manager.  If late fees are incurred on your travel card due late submission of your expense report, you will be responsible for payment of the late fees.  Attached you will find a user guide for the Concur system to assist you with completing the expense report.  Questions regarding expense reports can be sent to:
"
End If
    If ActiveCell.Offset(0, 5).Value = "y" Then
      NotsubHTML = NotsubHTML & "Please be advised that you have an expense report in BLANK for travel related to February 2012 that has not been submitted to your manager for approval. You must submit it for approval by 3/16/12 to your manager.  If late fees are incurred on your travel card due late submission of your expense report, you will be responsible for payment of the late fees.  Attached you will find a user guide for the Concur system to assist you with completing the expense report.  Questions regarding expense reports can be sent to:
"
End If
    If ActiveCell.Offset(0, 6).Value = "y" Then
      mealHTML = mealHTML & "The amount spent per person on Breakfast/Lunch/Dinner was over the allowed amount of XX.
"
End If
    If ActiveCell.Offset(0, 7).Value = "y" Then
      dupHTML = dupHTML & "Duplicate Warning.
"
End If
    If ActiveCell.Offset(0, 8).Value = "y" Then
      pastHTML = pastHTML & "Past Date.
"
End If
    If ActiveCell.Offset(0, 9).Value = "y" Then
      futureHTML = futureHTML & "Future Date.
"
End If
'Everything comes together
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .From = "BLANK@BLANKEMAIL"
        .To = ActiveCell.Offset(0, 12)
        .CC = ""
        .BCC = ""
        .Subject = "Expense Report Unassigned Transaction"
        'If we start using a standard file it can be attached like this
        '.Attachments.Add ("C:\test.txt")
        .Attachments.Add "J:\BLANK\BLANK\BLANK\BLANK.doc"
        .HTMLBody = " 
" & vbNewLine & _
                                vbNewLine & _
                                "" & vbNewLine & _
                                UnassHTML & vbNewLine & _
                                NotsubHTML & vbNewLine & _
                                "" & _
                                "
Just a quick reminder that you have un-submitted expenses in your BLANK queue from May.  Can you please create, finalize and submit your T&E for all expenses and/or credit card charges incurred prior to 5/24/12.

" & _
                                "If you are a BLANK travel card user, please be aware that unpaid credit card charges as shown on your most recent BLANK statement dated 5/25/12 which are 30 or more days past due are subject to late fees and potentially risking the status of your travel card. 

" & _
                                "Credit card charges must be imported into a T&E coinciding with the transactions reflected on your card statement and submitted once monthly prior to the 15th  to ensure timely payment.  BLANK'S next billing cycle will post on 6/25/12, past due balances will be noted on the new statement." & _
                                "If you are personally responsible for charges incurred, please remit payment direct to BLANK and let me know that they have been paid so that I can remove them from your Concur account.

" & _
                                "Attached you will find a user guide for the Concur system to assist you with completing your report.  Should you require additional assistance, please contact: 
" & _
                                "BLANK@BLANKEMAIL 

" & _
                                "Your immediate attention to this matter is greatly appreciated!
Thank you!"

        .Display
        'or use .Send if you DO NOT want to review it
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[RedMonkey]

Kind of a bump but I keep finding apparent solutions in the form of adding .Body to the email generation (which would require removal of the HTML body sections). The problem is it keeps being shown as a part of another process or with a massive assumption.

The clearest version had .Body = String2 & vbCr & vbCr & String3
With no method of knowing how the String2 and such were being defined other than;
String2 = "Name" & vbCr & "Title" & vbCr & "Address" </SPAN>
String3 = "Direct Line: +44(0)..."

Rather frustrating but this feels really close to what i'm working on.

 

[RedMonkey]

Just a last bump then I'll let it expire naturally.