Friday, March 12, 2010

Using MailTo: in a VBA macro

So yesterday I wrote a VBA macro where I wanted the end user to be able to send me some information generated by the tool the macro is a part of. I am pretty good at programming Outlook to do things for me using VBA, but I don't want to assume that's what the end user uses.

The alternative I came up with was using the mailto: command or link or whatever it is. It's the same thing you see in a web page when someone embeds their email address, you click it, and Windows pops up your default email editor with the author specified in the to: line. In a search I came up with this page that shows how you can not only specify the to: line, but also the subject and body. Perfect for what I want to do.

So all I had to do was make a VBA function to build a nice mailto: line based on input, and here's what I came up with:

Public Sub StartEmail(ToAddr As String, Optional Subject As String, Optional Body As String)
Dim URL As String
URL = "Mailto:" & ToAddr
If Subject <> "" Then
URL = URL & "?subject=" & Subject
If Body <> "" Then
URL = URL & "&body=" & Body
End If
ElseIf Body <> "" Then
URL = URL & "?body=" & Body
End If

Navigate URL
End Sub

The function simply takes in a mandatory to: argument, and optional subject and/or body, and builds a mailto: link like this.

The last link in this whole chain is calling the mailto: link so Windows can deal with it. I just call a sub I call navigate, which is really just calling the Windows ShellExecute API function. I just set it up in a separate module in pretty much any project I do because i usually end up using it for something, usually just letting Windows deal with opening a file or link, instead of me having to deal with it.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal _
lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As _
Long

Private Const SW_SHOW = 1

Public Sub Navigate(ByVal NavTo As String)
Dim hBrowse As Long
hBrowse = ShellExecute(0&, "open", NavTo, "", "", SW_SHOW)
End Sub


So yeah, anyway, now I have a nice easy one-line call to open up an email with whatever I want filled in. To be honest I've only tried it on my work machine which has Outlook as the default mail app, so your mileage may vary with other apps.

3 comments:

  1. I know this is an old post, but this is a function I use to create emails using mailto: (Work great if person's default is a web email program, like Gmail)

    Sub MailTo(Optional sTo As String, Optional sCC As String, Optional sBCC As String, Optional sSubject As String)

    ThisWorkbook.FollowHyperlink "mailto:" & IIf(sTo <> "", sTo, "") & "?" & IIf(sCC <> "", "&cc=" & sCC, "") & IIf(sBCC <> "", "&bcc=" & sBCC, "") & "&subject=" & sSubject

    End Sub

    You could add the body in there if you wanted as well but I leave it out so the person's default email signature shows up. If you put in the body and there is a default signature it might not show...

    ReplyDelete
    Replies
    1. So great! so few code to do it.
      do you know how to do to add attached file?
      Thanks so much

      Delete
  2. How can I run this macro?
    Tell me an example please

    ReplyDelete