Refer to cell in HTMLBody test in VBA code?

[Jyggalag]

Hi all,

I currently have an email macro in VBA that looks like this (important parts only):

Basically, I click on a macro and it sends out an email to the emails listed in certain cells. However, the message that the email depicts has been written in VBA with the .HTMLBody code.

Does anybody know how I can edit my code? Right now it goes "Dear all, in accordance with bla bla, please find the updated file for the period covering XYZ - XYZ 2".

I want to make a cell that returns a value showing the current week that we are in and the week number from last week and then refer to these when I say XYZ and XYZ 2 above.

Does this make sense? Can somebody help me do it please?

Thank you!

Jyggalag

 

[Jyggalag]

Oh by the way,

If there is a way in which you can write the ENTIRE HTMLBody in a cell and then refer to it, that would be amazing!

Kind regards,
Jyggalag

 

[GWteB]

You might use some placeholders (which should be unique whitin the text) and replace them with the values taken from your worksheet.
Something like this:

    Const PLHCURRENTWEEK    As String = "@&$CurrentWeek#!@"
    Const PLHLASTWEEK       As String = "@&$LastWeek#!@"
    
    Dim Body As String, Rng As Range
    
    Body = "Dear all, <br><br>In accordance with bla bla, please find the updated file for the period covering " & PLHLASTWEEK & " - " & PLHCURRENTWEEK & "."
    
    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1") ' <<< cell with number of lastweek; change to suit
    Body = VBA.Replace(Body, PLHLASTWEEK, Rng.Value)

    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("B2") ' <<< cell with number of current week; change to suit
    Body = VBA.Replace(Body, PLHCURRENTWEEK, Rng.Value)

    .HTMLBody = Body

 

[Jyggalag]

You might use some placeholders (which should be unique whitin the text) and replace them with the values taken from your worksheet.
Something like this:

    Const PLHCURRENTWEEK    As String = "@&$CurrentWeek#!@"
    Const PLHLASTWEEK       As String = "@&$LastWeek#!@"
   
    Dim Body As String, Rng As Range
   
    Body = "Dear all, <br><br>In accordance with bla bla, please find the updated file for the period covering " & PLHLASTWEEK & " - " & PLHCURRENTWEEK & "."
   
    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1") ' <<< cell with number of lastweek; change to suit
    Body = VBA.Replace(Body, PLHLASTWEEK, Rng.Value)

    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("B2") ' <<< cell with number of current week; change to suit
    Body = VBA.Replace(Body, PLHCURRENTWEEK, Rng.Value)

    .HTMLBody = Body

Hi,

How would that work? I just put this code in and then htmlbody = body will understand to refer to those cells?

Thank you!

 

[ebea]

If you, a little higher up in your code, write, by example:

MailBody = "Covering weeks: " & Range("D1") & " and " & Range("E1")

And down in your .HTMLBody just set: HTMLBody = mailBody

Where D1 and E1 are your week numbers

 

[GWteB]

How would that work? I just put this code in and then htmlbody = body will understand to refer to those cells?

The suggested code only shows one way to compose the body of your email using variable text components.
In your case, the variable components are in a worksheet range.
The effect of @ebea's code is similar, although the worksheet ranges in his code are not qualified.
This means that the values will be pulled from the active worksheet.