Emails via Excel  
Author Message
Randall155





PostPosted: Visual Basic for Applications (VBA), Emails via Excel Top

Hey Ladies and Gents,

I am a bit new to the world of visual basic so please be kind.

I have been set a mini-project here at work and need some assistance. What i am trying to achieve is for a user to click a command button in excel, opening up a new mail message in outlook. This new mail message will include filled areas in to, subject and main body with a hyperlink to the document in which they have just updated.

I have managed to produce the following code so far:

Sub Button1_Click()

Dim theApp, theNameSpace, theMailItem, myAttachment, MessageBody, subject

'create a new Outlook Application Object,
'direct it to the proper NameSpace,
'create a new Mail Item and set the attachments collection

Set theApp = CreateObject("Outlook.Application")
Set theNameSpace = theApp.GetNamespace("MAPI")
Set theMailItem = theApp.CreateItem(0)
theMailItem.Display
Set myAttachment = theMailItem.Attachments
MessageBody = "Please note the following file has now been updated {First Name Surname}"
subject = "subject information"

'add recipients to MailItem

")
theMailItem.subject = subject
theMailItem.Body = MessageBody

End Sub

I need some help with the body format e.g. paragraphs and what/how do i need to implement a hyperlink in the body of the email

Hopefully some of you can help

Thanks

Richard



Microsoft ISV Community Center Forums1  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Emails via Excel Top

If your default Outlook setting is to compose new emails as HTML, I think you can just send HTML-formatted text to the MessageBody like this:

Dim strMyMsgBody As String
strMyMsgBody = "Dear Sirs,<br>"

theMailItem.Body = strMyMsgBody

Chr(34) is the double-quotation mark, so you need to include it as in the above example on either side of anything that would normally be in quotes in HTML.

Does this help



 
 
Randall155





PostPosted: Visual Basic for Applications (VBA), Emails via Excel Top

Hey duck thing,

Thank you for you help so far.

I am bit of a newbie at all this so would it be possible for you to show me exactly where i would insert this into my coding

Then i shall give this a test run and keep you posted on my results

Thanks

Rich


 
 
Jon Peltier





PostPosted: Visual Basic for Applications (VBA), Emails via Excel Top

Excel MVP Ron de Bruin has a lot of information about emailing from Excel on his web site. You might get what you need all at once, rather than a drip at a time here:

http://www.rondebruin.nl/sendmail.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


 
 
Randall155





PostPosted: Visual Basic for Applications (VBA), Emails via Excel Top

I have taken a look at the link which you provided but dont feel any of the info on that site will help (please correct me if i am wrong). Like i said before i am totally new to this and feel i have done quite well so far. Please could someone assist me or show me how i could make up the body of the email using HTML as suggested by 'duck thing'

Please help me

Rich