Emails via Excel

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.Recipients.Add ("user@company.co.uk")
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

Answer this question

Emails via Excel

Tryin2Bgood

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>"
strMyMsgBody = strMyMsgBody & "<a href=" & Chr(34) & "mailto:nobody@nobody.com" & Chr(34) & ">Send me mail.</a>"
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
mjj0000
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
WIreD 0x90
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
_______
```
MDesigner
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
Emails via Excel

Answer this question

Emails via Excel

Hot Topic

Microsoft ISV

Emails via Excel

Emails via Excel

Tryin2Bgood

mjj0000

WIreD 0x90

MDesigner