Email 'Do It All Super' Button

Jojothemonk3y

New Member
Joined
Feb 1, 2017
Messages
5
Hello!

I would like to send an email from excel that has a pre-populated body of text that but also pulls some data from an excel sheet to populate parts of the email.

So for example, you would click the macro....a prompt would ask for a row number and based on that, the email box would pop with a prewritten body that would pull information from specified columns in the sheet that completes certain sentences (eg Your order ref (contents of B4)). Additionally it would also attach a specified document from a local drive It wouldn't need to populate the email address.

I'm not sure if this is possible? Any ideas?

Thanks in advance!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
604
Office Version
2016
Platform
Windows
Try something like this. MyRow stores the selected row, and you can build up your message in MyMsg using HTML. The message in the example is very basic, but you can use the same HTML elements as you would find in webpages. I've used a fixed email address to send to, but you could pick this up from the spreadsheet too.
Code:
<hmtl>[COLOR=#000000][FONT=-apple-system]Dim OlObj As Object[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]Set OlObj = CreateObject("Outlook.Application")[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]Set OlItem = OlObj.CreateItem(olMailItem)[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]Set OlAttachments = OlItem.Attachments[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]MyRow = InputBox("Enter row number")[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]With OlItem[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system].To = "[/FONT][/COLOR][EMAIL="name.surname@company.com"]name.surname@company.com[/EMAIL][COLOR=#000000][FONT=-apple-system]"[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system].Subject = "Email Subject"[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]MyMsg = "<hmtl>"</hmtl>[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]MyMsg = MyMsg & "Your order ref: " & Range("B" & MyRow).Value & 
"[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]MyMsg = MyMsg & ""[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system].HTMLBody = MyMsg[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system].Send[/FONT][/COLOR]
[COLOR=#000000][FONT=-apple-system]End With[/FONT][/COLOR]
EDIT: Please ignore the "<html>" at the start of the first line of the code, as well as the "</html>" at the end of this note! I didn't type them, and they don't appear when I try to edit the message - but for some reason they are being added when I submit the post!</hmtl>
 
Last edited:

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
604
Office Version
2016
Platform
Windows
Sorry, the code is still being displayed incorrectly - I'll try again!
Code:
Dim OlObj As Object
Set OlObj = CreateObject("Outlook.Application")
Set OlItem = OlObj.CreateItem(olMailItem)
Set OlAttachments = OlItem.Attachments
MyRow = InputBox("Enter row number")
With OlItem
.To = "name.surname@company.com"
.Subject = "Email Subject"
MyMsg = "<hmtl>"
MyMsg = MyMsg & "<p>Your order ref: " & Range("B" & MyRow).Value & "</p>"
MyMsg = MyMsg & "</html>"
.HTMLBody = MyMsg
.Send
End With
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,736
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top