Struggling with "Mail Body"

K

kizzie37

Well-known Member
Joined
Oct 23, 2007
Messages
585
Office Version
  1. 365
HI

I have the following VB code for my "mail Body" but VB wont accept any of the Mail body text (all red) keeps erroring with a compile error
VBA Code: 
'Mail Body

strBody = "<BODY style = font-size:14pt; font-family:Arial>" & _

"Dear " & ActiveCell.Offset(0, 2).Value & _

"<br><br>I am pleased to update you that " & ActiveCell.Offset(0, 1).Value & " will be joining your team effective" _

& HireDate & " To make their onboarding process smooth and effective, please utilize the "https://xxxxxxxxxxxxxx/:b:/s/xxxxxxxxxxxxxxxxxxxxxxxxx/EQ_JtLEpoflGivhsvhgtgJEBXcjsmBwP2rhT06CzdFH3xw?e=ORyVCK""> Onboarding Checklist </a>" and "https://xxxxxxxxxxxxxxxxxxxm/:b:/s/xxxxxxxxxxxxxxxx/ESaLR9geS9ZOq3eKqq2XoTUBO0kvNMOkDibFLi7vJeiksg?e=rqDdQh ""> Onboarding Playbook </a>" & _ for managers. This will be helpful in creating a structure onboarding process for your new hire." & _

"<br><br>Studies show that a buddy system can greatly help a new team member settle into their role quickly and get familiar with team's processes and culture. I believe it would be beneficial for "& ActiveCell.Offset(0, 1).Value & "  to have a dedicated buddy to guide them through any minor day to day operations questions they may have. This individual will be an experienced coworker who is well-versed into the culture of the organization. This relationship has the potential to instill a sense of belonging in the new employee and considerably speed up their process of becoming acclimated to the company." & _

"<br><br>Could you please let me know if there's a suitable team member who can be assigned as buddy for "& ActiveCell.Offset(0, 1).Value & _

"<br><br>Please also define your expectations and goals to the new hire within 7-10days of their joining. It is advised to set expectations like working hours, availability, proper protocol for meetings, Key Performance Indicators etc. at the very beginning." & _

"<br><br>Thank you for your help in this matter, let me know if you need any further information."

I think its how Im starting or ending each line but I cant figure out how to fix the issue at all, Can anyone help?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Line continuation characters with lots of concatenation is a pain because of the mix of quotes required - sometimes pairs, triplets or even quads. I think you will find this method much easier.
VBA Code: 
strbody = "<BODY style = font-size:14pt; font-family:Arial> & Dear" & ActiveCell.Offset(0, 2).Value
strbody = strbody & " " & "<br><br>I am pleased to update you that " & ActiveCell.Offset(0, 1).Value & " will be joining your team effective "
IMO, you can shorten code with <P> rather than <br><br>, plus you don't need to use .Value 99.9% of the time. I recommend that you put spaces at the beginning of a line when required so that you don't have to scan to the right to ensure they're there. That is, unless you prefer to eliminate the concatenation for spaces before variables. In that case, a space would go after Dear. I think I would use a range variable as well, instead of constantly repeating activecell with offsets.
 
Upvote 0
Micron said:
Line continuation characters with lots of concatenation is a pain because of the mix of quotes required - sometimes pairs, triplets or even quads. I think you will find this method much easier.
VBA Code: 
strbody = "<BODY style = font-size:14pt; font-family:Arial> & Dear" & ActiveCell.Offset(0, 2).Value
strbody = strbody & " " & "<br><br>I am pleased to update you that " & ActiveCell.Offset(0, 1).Value & " will be joining your team effective "
IMO, you can shorten code with <P> rather than <br><br>, plus you don't need to use .Value 99.9% of the time. I recommend that you put spaces at the beginning of a line when required so that you don't have to scan to the right to ensure they're there. That is, unless you prefer to eliminate the concatenation for spaces before variables. In that case, a space would go after Dear. I think I would use a range variable as well, instead of constantly repeating activecell with offsets.
Click to expand...
Hi, thanks, but how do i continue from the second sentence, its still erroring. Im only a beginner with VB
 
Upvote 0
You have to ensure each portion is properly delimited. You have 2 double quotes here e=ORyVCK""> which doesn't look right. Will take a further look in a bit.
 
Upvote 0
I am pretty sure part of the problem is how many lines you're attempting to add to the variable at one time. Also, it looked like your hyperlink references were incorrect. Please try this and see if it works for your MailBody:

VBA Code: 
Sub Kizzie()
'Code Reconstruction by Wookiee at MrExcel.com

Dim strBody As String

strBody = "<BODY style = font-size:14pt; font-family:Arial>"

strBody = strBody & "Dear " & ActiveCell.Offset(0, 2).Value

strBody = strBody & "<br><br>I am pleased to update you that " & _
   ActiveCell.Offset(0, 1).Value & " will be joining your team effective "

strBody = strBody & HireDate & ". To make their onboarding process smooth and effective, " & _
   "please utilize the <a href=""https://xxxxxxxxxxxxxx/:b:/s/xxxxxxxxxxxxxxxxxxxxxxxxx/" & _
   "<a href:=""EQ_JtLEpoflGivhsvhgtgJEBXcjsmBwP2rhT06CzdFH3xw?e=ORyVCK"">Onboarding Checklist</a>" & _
   "and <a href:=https://xxxxxxxxxxxxxxxxxxxm/:b:/s/xxxxxxxxxxxxxxxx/" & _
   "ESaLR9geS9ZOq3eKqq2XoTUBO0kvNMOkDibFLi7vJeiksg?e=rqDdQh"">Onboarding Playbook</a>" & _
   "for managers. This will be helpful in creating a structure onboarding process for your new hire."

strBody = strBody & "<br><br>Studies show that a buddy system can greatly help a new team " & _
   "member settle into their role quickly and get familiar with team's processes and culture. " & _
   "I believe it would be beneficial for " & ActiveCell.Offset(0, 1).Value & _
   " to have a dedicated buddy to guide them through any minor day to day operations " & _
   "questions they may have. This individual will be an experienced coworker who is " & _
   "well-versed into the culture of the organization. This relationship has the "

strBody = strBody & "potential to instill a sense of belonging in the new employee and " & _
   "considerably speed up their process of becoming acclimated to the company." & _
   "<br><br>Could you please let me know if there's a suitable team member who can be assigned as buddy for " & _
   ActiveCell.Offset(0, 1).Value & "<br><br>Please also define your expectations and goals " & _
   "to the new hire within 7-10days of their joining. "

strBody = strBody & "It is advised to set expectations like working hours, availability, " & _
   "proper protocol for meetings, Key Performance Indicators etc. at the very beginning." & _
   "<br><br>Thank you for your help in this matter, let me know if you need any further information."

End Sub
 
Upvote 0
Solution
The limit is 24 and I only see 8 so probably not an issue? In the OP the 3rd line is not ended correctly, so that's an issue as well as the misplaced quotes I mentioned (and any others I didn't find yet). Anyway, the result of the latest code is as follows (ignore the values it got from my sheet). Notice that some spaces are missing or in the wrong place.
Dear 3

I am pleased to update you that 5 will be joining your team effective 7/8/2024. To make their onboarding process smooth and effective, please utilize the Onboarding Checklistand Onboarding Playbookfor managers. This will be helpful in creating a structure onboarding process for your new hire.

Studies show that a buddy system can greatly help a new team member settle into their role quickly and get familiar with team's processes and culture. I believe it would be beneficial for 5 to have a dedicated buddy to guide them through any minor day to day operations questions they may have. This individual will be an experienced coworker who is well-versed into the culture of the organization. This relationship has the potential to instill a sense of belonging in the new employee and considerably speed up their process of becoming acclimated to the company.

Could you please let me know if there's a suitable team member who can be assigned as buddy for 5

Please also define your expectations and goals to the new hire within 7-10days of their joining. It is advised to set expectations like working hours, availability, proper protocol for meetings, Key Performance Indicators etc. at the very beginning.

Thank you for your help in this matter, let me know if you need any further information.
Click to expand...
I count about 7 errors, a few being grammatical in nature, plus one questionable one that I'm not sure of.
 
Upvote 0
Wookiee said:
I am pretty sure part of the problem is how many lines you're attempting to add to the variable at one time. Also, it looked like your hyperlink references were incorrect. Please try this and see if it works for your MailBody:

VBA Code: 
Sub Kizzie()
'Code Reconstruction by Wookiee at MrExcel.com

Dim strBody As String

strBody = "<BODY style = font-size:14pt; font-family:Arial>"

strBody = strBody & "Dear " & ActiveCell.Offset(0, 2).Value

strBody = strBody & "<br><br>I am pleased to update you that " & _
   ActiveCell.Offset(0, 1).Value & " will be joining your team effective "

strBody = strBody & HireDate & ". To make their onboarding process smooth and effective, " & _
   "please utilize the <a href=""https://xxxxxxxxxxxxxx/:b:/s/xxxxxxxxxxxxxxxxxxxxxxxxx/" & _
   "<a href:=""EQ_JtLEpoflGivhsvhgtgJEBXcjsmBwP2rhT06CzdFH3xw?e=ORyVCK"">Onboarding Checklist</a>" & _
   "and <a href:=https://xxxxxxxxxxxxxxxxxxxm/:b:/s/xxxxxxxxxxxxxxxx/" & _
   "ESaLR9geS9ZOq3eKqq2XoTUBO0kvNMOkDibFLi7vJeiksg?e=rqDdQh"">Onboarding Playbook</a>" & _
   "for managers. This will be helpful in creating a structure onboarding process for your new hire."

strBody = strBody & "<br><br>Studies show that a buddy system can greatly help a new team " & _
   "member settle into their role quickly and get familiar with team's processes and culture. " & _
   "I believe it would be beneficial for " & ActiveCell.Offset(0, 1).Value & _
   " to have a dedicated buddy to guide them through any minor day to day operations " & _
   "questions they may have. This individual will be an experienced coworker who is " & _
   "well-versed into the culture of the organization. This relationship has the "

strBody = strBody & "potential to instill a sense of belonging in the new employee and " & _
   "considerably speed up their process of becoming acclimated to the company." & _
   "<br><br>Could you please let me know if there's a suitable team member who can be assigned as buddy for " & _
   ActiveCell.Offset(0, 1).Value & "<br><br>Please also define your expectations and goals " & _
   "to the new hire within 7-10days of their joining. "

strBody = strBody & "It is advised to set expectations like working hours, availability, " & _
   "proper protocol for meetings, Key Performance Indicators etc. at the very beginning." & _
   "<br><br>Thank you for your help in this matter, let me know if you need any further information."

End Sub
Click to expand...
This worked great, thanks!
 
Upvote 0
kizzie37,

Please note: When marking a post as the solution, please mark the original post containing the solution, and not your own post acknowledging that some other post is the solution.
I have updated this for you on this thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
2 Mail Range/Selection in Outlook Body
Replies
1
Views
1K
marcusmark
M

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top