Pull a number from a cell and add to the subject of an email.

[Samuelal]

I have a button on a workbook template that will save the work book and attach it to an email. I want to incorporate the ID number into the subject of the email. This number is changed each time the template is used.

For example: If the ID number is 000001, I want the email subject to read "Workbook #000001: Approval Required"

My Code:

Private Sub CommandButton1_Click()
ActiveWorkbook.Save
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim PAAID As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

xMailBody = "Hello," & vbNewLine & vbNewLine & _
"The attached PAA Form is complete and needs approval. Please review and return with approval or revision comments." _
& vbNewLine & vbNewLine & _
"Best regards," _
& vbNewLine & vbNewLine & _
"Strategic Procurement"
On Error Resume Next
With xOutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"
.Body = xMailBody
.Attachments.Add (ActiveWorkbook.FullName)
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Other than getting the cell value in the subject line, the code works just fine.

 

[Joe4]

Welcome to the Board!

Is this the part you are having issues with?

.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"

If so, know that everything in between double-quotes is treated as literal text. So if you want to combine range values (or variables) with literal text strings, you need to "concatenate" those pieces together, like this:

.Subject = "PAA: PAAID = " & Range("Price Adjustment Approval!T2").Value & " Approval Required"

(the literal text parts are shown in blue, and the range value is shown in red, and they are concatenated with "&" symbols.

 

[Samuelal]

Welcome to the Board!

Is this the part you are having issues with?

.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"

If so, know that everything in between double-quotes is treated as literal text. So if you want to combine range values (or variables) with literal text strings, you need to "concatenate" those pieces together, like this:

.Subject = "PAA: PAAID = " & Range("Price Adjustment Approval!T2").Value & " Approval Required"

(the literal text parts are shown in blue, and the range value is shown in red, and they are concatenated with "&" symbols.

Welcome to the Board!

Is this the part you are having issues with?

.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"

If so, know that everything in between double-quotes is treated as literal text. So if you want to combine range values (or variables) with literal text strings, you need to "concatenate" those pieces together, like this:

.Subject = "PAA: PAAID = " & Range("Price Adjustment Approval!T2").Value & " Approval Required"

(the literal text parts are shown in blue, and the range value is shown in red, and they are concatenated with "&" symbols.

Thank you for the welcome!

I tried what you said, but now when the email window pops up, the subject line is completely blank.

.Subject = "PAA:" & Range("Price Adjustment Approval!T2").Value & "Approval Required"

 

[Joe4]

Try something simple first, to see if it works, i.e.

.Subject = "Test"

Then if that works, try adding some complexity into it, bit-by-bit.

 

[Samuelal]

Try something simple first, to see if it works, i.e.

.Subject = "Test"

Then if that works, try adding some complexity into it, bit-by-bit.

I entered:

.Subject = "PAA: Approval Required"

This is where I started from and that works. Now I want to get the ID number in the middle of it.

 

[Joe4]

OK, why not try building the Subject line in a string, like you did with the body of the email, where you can use message boxes to make sure you are returning what you want before assigning it to the Subject line, i.e.

Dim xSubject as String
...
xSubject = "This is a test" & Range("A1").Value
MsgBox xSubject
...
.Subject = xSubject

Also, is that "PAAID" in your subject line supposed to be literal text, or some variable value that is sent somewhere (I see it declared in the variable section)?

Maybe you could go through each part of your original attempt, and let us know what each piece is, and where it comes from.

.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"

Also, what exactly is in Range("Price Adjustment Approval!T2")?

 

[Samuelal]

OK, why not try building the Subject line in a string, like you did with the body of the email, where you can use message boxes to make sure you are returning what you want before assigning it to the Subject line, i.e.

Dim xSubject as String
...
xSubject = "This is a test" & Range("A1").Value
MsgBox xSubject
...
.Subject = xSubject

Also, is that "PAAID" in your subject line supposed to be literal text, or some variable value that is sent somewhere (I see it declared in the variable section)?

Maybe you could go through each part of your original attempt, and let us know what each piece is, and where it comes from.

.Subject = "PAA: PAAID = Range("Price Adjustment Approval!T2").Value Approval Required"

Also, what exactly is in Range("Price Adjustment Approval!T2")?

Creating the subject as a string worked, thank you!

The "PAAID" definition was from an earlier attempt.
The "Range("Price Adjustment Approval!T2") was my best guess at how to pull the value from cell T2 in the workbook page 'Price Adjustment Approval'

I've had no VBA experience until this project called for it. This was a huge help!

 

[Joe4]

Creating the subject as a string worked, thank you!

You are welcome.
So it sounds like you are all set. If otherwise, please let me know.