CODEWITHSUNDEEP
excelvba
Scott Cronin
Scott Cronin

Reputation: 23

How can I call a cell value within a string?

I'm trying to set up a macro to send an email, updating people on a due date timeline. This email should pull dates from specific cells and place them into the message body. So far I'm able to set up the email to read the way I want, but am having trouble calling the dates.

I'm super new at vba and this may not be a function that's possible, but any workarounds would be appreciated! Thanks!

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Dim rev_1 As Date, rev_2 As Date, final_due As Date
rev_1 = Range(I2)
rev_2 = Range(K2)
final_due = Range(M2)

strbody = "XXXXXXX" & vbNewLine & vbNewLine & _
          "This is an update on your project dates:" & vbNewLine & vbNewLine & _
          "Review 1 Suspense: CALL CELL I2 HERE" & vbNewLine & _
          "Review 2 Suspense: CALL CELL K2 HERE" & vbNewLine & _
          "Final Due Date: CALL CELL M2 HERE" & vbNewLine & vbNewLine & _
          "Acoording to this timeline, you are AHEAD/ON TIME/BEHIND." & vbNewLine & vbNewLine & _
          "If you have any questions, please contact XXXX." & vbNewLine & vbNewLine & _



On Error Resume Next
With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Upcoming Project - Timeline Update"
    .Body = strbody
    .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Upvotes: 2

Views: 465

Answers (1)

h2so4
h2so4

Reputation: 1577

hello you could do like this

"Review 1 Suspense: " & range("I2") & vbNewLine & _

Upvotes: 1

Related Questions