Trouble with VBA email

[bts73]

Hello. I'm working on a project that requires a button click to launch an Outlook message box with a pre-filled template. I have had success with using the =hyperlink("mailto") function, but the contents of the message exceed 255 characters. I'm very novice at VBA script, and although I have tried a whole bunch of things I found online, but so far nothing has worked, at least without throwing errors at me that I personally can't debug.

For refernce here are the cell values I am using:

To: D30
CC: H31
Suject: D35
Body: F41 (I have concatenated the whole message here, but I would be interested in seeing how to combine text and cell values in VBA script: ex: "Hello", (A1) "Thanks")


Many thanks in advane for any advice you can offer.

 

[Kenneth Hobson]

Welcome to the forum!

Ron de Bruin has several examples using Outlook or CDO. Here is one for Outlook: https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

 

[bts73]

Tanksfe quick respose! I have actually tried a few things from that site, but am unable to manipulate this wthout erors, literally the script is stopping at the top line where the sub command sits. ...and because I am so novice to this, I don't know where to start from a debugging perspective.

 

[DanteAmor]

Try this:

Body: "Hello: " & Range("A1").Value & " Regards " & Range("C2").Value & " .Thanks"

 

[Kenneth Hobson]

Click # icon on reply toolbar and paste code between code tags so we can debug.

 

[bts73]

[LEFT][COLOR=#000000][FONT=Courier]Sub CustomMailMessage()[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]Dim OutApp As Outlook.Application[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]Dim objOutlookMsg As Outlook.MailItem[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]Dim objOutlookRecip As Recipient[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]Dim Recipients As Recipients[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Set OutApp = CreateObject("Outlook.Application")[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Set objOutlookMsg = OutApp.CreateItem(olMailItem)[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Set Recipients = objOutlookMsg.Recipients[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Set objOutlookRecip = Recipients.Add("alias@domain.com")[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  objOutlookRecip.Type = 1[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  objOutlookMsg.SentOnBehalfOfName = "sales@domain.com"[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  objOutlookMsg.Subject = "Testing this macro"[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  objOutlookMsg.HTMLBody = "Testing this macro" & vbCrLf & vbCrLf[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  'Resolve each Recipient's name.[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  For Each objOutlookRecip In objOutlookMsg.Recipients[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]    objOutlookRecip.Resolve[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Next[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  'objOutlookMsg.Send[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  objOutlookMsg.Display[/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier][/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]  Set OutApp = Nothing  [/FONT][/COLOR][COLOR=#000000][FONT=Courier]
[/FONT][/COLOR][COLOR=#000000][FONT=Courier]End Sub[/FONT][/COLOR][/LEFT]