Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro to launch Outlook from Excel

Status
Not open for further replies.
abitslow

abitslow

Technical User
Apr 25, 2003
44
I can't seem to work out the code for launching Outlook from Excel. I want to assign a macro to a button which will open a mail message addressed to me. Excel doen't seem to recognise the commands. My attempt so far:
Code: 
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Dim eglAdd As Outlook.Recipient

'Create an instance of Outlook
Set olApp = CreateObject("Outlook.Application")
olApp.Visible = True

'Create email Item
Set olMsg = OLApp.CreateItem(olMailItem)

With OLMsg
    'Set body format to HTML
    .BodyFormat = olFormatHTML
    .HTMLBody = "<HTML><H2>The body of this message will appear in HTML.</H2><BODY>Please indicate your problem below:</BODY></HTML>"
    .Display
End With

Set eglAdd = OLMsg.Recipients.Add("info@expgeo.co.uk")

End Sub

Many thanks in advance,
Matt.
 
Sort by date Sort by votes
Matt

I think that you should add the recipient, before you display the mail item
 
Upvote 0 Downvote
Thanks Jerry but unfortunately it still errors:
"User-defined type not defined" on the first line!
 
Upvote 0 Downvote
menu Tools -> References ...
tick the Microsoft Outlook Object Library.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
This is how I have mine working. I'm shelling it instead of operating through the object.

Code: 
dim sURL as string

'Create the email
sURL = "mailto:" & sEmailRecipient & "?subject=" & sSubject & "&body="
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Then use appliction.sendkeys "" to submit. I forgot the key to send on that one since I'm editing mine and manually sneding it.

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]
 
Upvote 0 Downvote
Matt

If you declare those variables to be of Object and substitute the value of the constants olFormatHTML and olMailItem (=2 and 0) then you are using late binding which doesn't have any versioning problems but lacks the ability to use intellisense opposed to early binding. For the second, you need to reference the library, as PHV mentioned
 
Upvote 0 Downvote
Excellent. Thanks Pink & Jerry. I'll take a look at both.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
330
strongm
strongm
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
561
remeng
remeng
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
1K
Andrzejek
Andrzejek
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
296
dhookom
dhookom
Andrzejek
  • Locked
  • Question
Retain signature in Outlook
Replies
17
Views
537
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top