Standard e-mail body text used in multiple macros

[fangfacekitty]

I have several macros in a workbook to send e-mails to different address groups depending on specific scenarios. The e-mail body text will always be the same. What I want to do is have the body text written in just one single location and not in every macro so that when someone wants to change the text later on he/she can change it in 1 place and all the macros will be correct.

The workbook users know nothing about VBA and in some cases almost nothing about Excel so I am trying to make this as simple as possible for them to maintain.

This is the e-mail section of one of the macros:

Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143

Else
'You use Excel 2007-2010
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set OutApp = CreateObject("Outlook.Application")
For Each sh In ThisWorkbook.Worksheets
If sh.Range("D30").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = sh.Range("C2").Value & " " _
& Format(Now, "yy-mmm-dd")
Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = sh.Range("D30").Value
.CC = sh.Range("D35").Value
.BCC = ""
.Subject = TempFileName
.Body = "Dear " & sh.Range("C29").Value & "," & vbNewLine & vbNewLine & _
"See attached pull sheet requirements which represent the detailed routing instructions including window times for order, tendor, ship and delivery transportation requirements." & vbNewLine & _
" " & vbNewLine & _
"Any deviation from the pull sheet requirements should be communicated to the Plant Material Coordinator prior to shipment with action plan." & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Regards," & vbNewLine & _
"Materials Group"
.Attachments.Add wb.FullName

 

[daverunt]

Hi,

I use a fixed messages in the same workbook as my email macros.

I just merged the cells of one column e.g. G8 to G44 and layout the mail in that.
The writing of text into a merged Excel box is a little awkward but as I don't chage it months at a time it is no great issue.

I use the code below to concatenate the cells in the range for the message body.

'Message create
    For Each Cell In Sheets("Messages").Range("G8:G44")
        MailBody = MailBody & Cell.Value & vbCr
    Next

 

[fangfacekitty]

Thanks. I was not able to get this to work for me but I was able to use your idea about putting the e-mail body in a cell. I deleted the specific text from the macros and now just refer to the cell value as I do for the e-mail addresses.