Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: mailto: hyperlink using macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i am able to easily create hyperlinks in excel to create a mailto: link such as:

    "mailto:someone@somewhere.com?subject=Comments from Mailto Syntax Page&body=this is the body"

    can someone help me create a macro that will generate the same Outlook email message?
    thanks.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you using Outlook or Outlook Express? I can give you code for the former, but not the latter. This code sends an email from Outlook - let me know how you get on.

    Sub CreateAnAutoEmail()
    Dim olApp As Object, olMail As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.createitem(0)

    olMail.to = "someone@somewhere.com"
    olMail.body = "This is the body"
    olMail.Subject = "And here is the subject"

    'You can specify other properties e.g.

    olMail.Attachments.Add "C:temppoo.txt"
    olMail.DeleteAfterSubmit = True

    olMail.Send

    'and so on, there are loads of them.
    End Sub


    D

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks very much...does the kind of thing i am looking for.
    You said there were loads of them...is there a listing on the web of commands for this?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The way to get to all of the properties is to do this:-

    In the VB Editor click Tools, References and choose Microsoft Outlook n.x Object Library (where n and x will depend on your version).

    You can then change your code to this:-

    Code:
    Sub CreateAnAutoEmail()
    Dim olApp As Outlook.Application, olMail As Outlook.MailItem
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = "someone@somewhere.com"
    olMail.Body = "This is the body"
    olMail.Subject = "And here is the subject"
    
    'You can specify other properties e.g.
    
    olMail.Attachments.Add "C:temppoo.txt"
    olMail.DeleteAfterSubmit = True
    
    olMail.Send
    
    'and so on, there are loads of them.
    End Sub
    What you've done now is used early binding, rather than late binding (I used late binding for simplicity). Setting a reference to the Outlook object library means that all constants, properties,methods and events of Outlook will now be available to your application. For example, in your code after the line olMail.Subject type olMail. as soon as you type the full stop (or period but I'm English ) all of the properties and methods of the Outlook Mailitem will be shown in a drop down list. It's probably best to just play around and experiment until you get your code the way you want.

    Another way of seeing what's available is to use the object browser (F2). Here you can view all classes currently available to your project. It may seem a little confusing if you haven't used it before but once you understand it, it's one of the most useful tool in developing your apps. If you have any more questions just ask.

    Regards,
    D


    [ This Message was edited by: dk on 2002-02-25 10:19 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    when trying to use this piece of code:

    Sub CreateAnAutoEmailNOTWORKING()
    Dim olApp As Outlook.Application, olMail As Outlook.MailItem
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.To = "someone@somewhere.com"
    olMail.Body = "This is the body"
    olMail.Subject = "And here is the subject"
    olMail.Send
    End Sub

    i am getting the error message:
    "User-defined type not defined"
    for the section:
    olApp As Outlook.Application
    it works fine in the orginal workbook i was testing it in, but now that i have copied it to a new workbook, it doesn't.
    Thanks for you help.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-25 10:14, dk wrote:
    In the VB Editor click Tools, References and choose Microsoft Outlook n.x Object Library (where n and x will depend on your version).
    Remember to set this reference.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •