CODEWITHSUNDEEP
vbaexcelemailvariables
Baz
Baz

Reputation: 21

How do I insert the value of a String variable into some text that will end up in the body of an email?

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.

Upvotes: 2

Views: 59456

Answers (1)

luke_t
luke_t

Reputation: 2985

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.

Upvotes: 13

Related Questions