How do I insert the value of a String variable into some text that will end up in the body of an email?
strbody = "Hi guys," & vbNewLine & vbNewLine & _
"Please can you issue the following:" & vbNewLine & vbNewLine & _
"Part number: " & PartNumber & vbNewLine & _
"Qty: " & Qty & vbNewLine & _
"This is line 4"
Just include the PartNumber
and Qty
variable names inside the part of code where you're creating the e-mail body string; remember to use the &
operator to join string variables together.
Admin
Updated on July 22, 2022Comments
-
Admin almost 2 years
I have a spreadsheet that is going to be used to track requests made to another department. I would like a Macro to generate and send an email the contains some predefined text and the value of some variables. I already have some working code that scans the relevant cells and stores their values.
I can generate the email, I can print lines of text including inserting one variable into the subject, but I can't seem to insert the value of any of the variables in the middle of the body of the email. I have the following:
Sub IssueRequest() Dim OutApp As Object Dim OutMail As Object Dim strbody As String ' Selecting the last entry in column "B" Range("B7").Select ActiveCell.Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop ActiveCell.Offset(-1, 0).Select ' Collect the unique value to insert into the subject field Dim Subject As String Subject = ActiveCell.Value ActiveCell.Offset(0, 2).Select ' Collect the Part Number for use in the body of the email Dim PartNumber As String PartNumber = ActiveCell.Value ' Collect the Quantity for use in the body of the email ActiveCell.Offset(0, 1).Select Dim Qty As String Qty = ActiveCell.Value 'Create the email Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "Hi guys," & vbNewLine & vbNewLine & _ "Please can you issue the following:" & vbNewLine & vbNewLine & _ "Part number: " & vbNewLine & _ "Qty: " & vbNewLine & _ "This is line 4" On Error Resume Next With OutMail .To = "[email protected]" .CC = "" .BCC = "" .Subject = Subject .Body = strbody .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub*
I really need to be able to insert the values of PartNumber and Qty in the middle of the String strbody.