Can you store .HTMLBody in a cell and handle with variable?

[tbakbradley]

I have a spreadsheet that will auto send an email, attaching the current spreadsheet after the user is asked to save the document. The email works fine with everything hardcoded. I even included email addresses on another spreadsheet on a server that this WB points to so the To and CC are always up to date Incase someone needs to update who we send the email to.

I was hoping I could store the .HTMLBody section I currently have hardcoded into a cell elsewhere on the Workbook. Ultimately I’d store it on another Workbook on a server and point there, but for this example, I’ll use the same workbook

Within the macro, I have set this variable, however, I did not declare this variable anywhere.

Subj= Sheets(“Sheet1”).Range(“A1”).Value

Where this cell, I just copied the hardcoded htmlbody section that works fine, and pasted into A1. That includes all the quotation marks, html tags etc.

Then I have

.htmlbody = Subj

Nothing comes over into the body of the email so I may not be able to handle it this way. My hope was to store the htmlbody elsewhere, with all the tags, and point to it. That way, if we had a reason to change the common body for this email, I could update it on that cell.

Sorry for the sloppiness here. I’m on my phone and not near my work PC.

 

[Coding4Fun]

This is my go to for this topic

https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[tbakbradley]

This is my go to for this topic

https://www.rondebruin.nl/win/s1/outlook/mail.htm

I had been to that site through research I did, but that didn't have what I needed unless I missed it.

My Body will have Text and Variables. It works fine hard coded. But when I just pasted all that data into a Cell on the Spreadsheet, and then set that Cell into the .HTLM Body, it didn't come over correctly and didn't produce the results of the variables. I guess I don't know how to store it all in a Cell.

cs = Sheet22.Range("FTTICover_CaseNumber").Value 'Case Number for Subject 
st = Sheet22.Range("FTTICover_State").Value 'State for Subject and Body 
Lo = Sheet22.Range("FTTILocations").Value 'Location Number for Body
cn = Sheet22.Range("FTTICover_Customer").Value 'Customer Name for Subject


With OutlookMail.To = Sheet5.Range("AM2").Value 
.CC = Sheet21.Range("FTTIGCNEEmail").Value 
.Subject = cn & " " & "-" & " " & "FTTI Bulk Upload Case" & " " & cs
.BodyFormat = 2
.HTMLBody = [COLOR=#ff0000]"<b>FIM IT</b><br><br>" & "Hello, Will you please run the attached Bulk Upload for<b>" & " " & Lo & "</b> sites in<b>" & " " & st & " " & "</b>through FIM using the FIOS Query?<BR><BR>" & "<img src='\\Server\Share\Folders\QueryImage.jpg'>"[/COLOR]
.Attachments.Add ActiveWorkbook.FullName
.display 
End With


Set OutlookMail = Nothing
Set outlookApp = Nothing

Everything in Red, I would like to Store into a Cell in the Workbook. I tried copying and pasting it directly and references that cell in the .HTMLBody, but didn't work correctly.

 

[daverunt]

Hi,

If I am reading you correctly.

If the cell is formatted as text you can have the html tags/content

< p>Hi</p>
< p>Here are the quotes for your devices< br>< br>John< /p>

You can then reference it in a variable, ands use .HTMLBody

strbody = Range("C16").Value

.HTMLBody = strbody

 

[daverunt]

Sorry didn't read second part where variables also stored in cell.
Not sure how that could be achieved as the variables would just come across as text. They would need specifying in the code.

 

[tbakbradley]

Sorry didn't read second part where variables also stored in cell.
Not sure how that could be achieved as the variables would just come across as text. They would need specifying in the code.

Thank you. That was the part I was having difficulties with....the Variables. But I'm working on a different approach now. I was going to have the emails sent Automatically, but I'm going to build a User Form where the User can select "Send Email Now" or "Open Email and Send Manually" something like that. That way, if something were to change to the process of the email prior to me updating the tool and having it uploaded, the User could choose to Open the Email first, and manually make the changes. So Hard Coding will be just fine I think.

 

[Coding4Fun]

Yeah I did not fully understand what you were trying to do either and I also do not see a way this can be achieved.

If you happen to find one please post back but it sounds like you are taking a different approach.

Best of Luck.