Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Compare Methods: FollowHyperlink  vs CreateObject(OutlookApplication) to generate an email from Excel using VBA

Posted on 2013-11-18
5
Medium Priority
?
1,091 Views
Last Modified: 2013-11-18
Can someone please enlighten me?

How do the methods "CreateObject" and Followhyperlink compare, when generating emails from Excel, using VBA?

The following examples seem to do the same thing.

1: Using CreateObject Method

Sub newemail()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)

    On Error Resume Next
    With OutMail
        .To = "person@email.com"
        .Subject = "File Review"
        .body = "Please review file located at: " & _
                vbCrLf & "<file://" & ActiveWorkbook.FullName & ">"
        .display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window


2: Using FollowHyperlink Method

Sub CreateEmail()
     
    Dim rcp, hlink, msg, subj
    
    rcp = "person@email.com"
    subj = "Please review this file"
    msg = "Please review file located at: " & _
    "%0A%0A" & "<file://" & ActiveWorkbook.FullName & ">"
    
    hlink = "mailto:" & rcp & "?"
    hlink = hlink & "subject=" & subj & "&"
    hlink = hlink & "body=" & msg
    
    ActiveWorkbook.FollowHyperlink (hlink)
End Sub

Open in new window



Is one method more robust than the other?
Is one method more versatile than the other?
What other Methods would it be useful to know about?
HAve I asked the right questions?

Many thanks
David
0
Comment
Question by:David Phelops
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 2000 total points
ID: 39655834
Hi David,

The fundamental difference between the two approaches is that you are explicitly using Microsoft Outlook when creating an object, but following a hyperlink ("mailto:" protocol) will create an e-mail within the default e-mail application on the run-time machine.  This may not be MS-Outlook, of course.

In fact, MS-Outlook may not be present on the run-time machine.  If this was the case in the first approach, then the code would fail.  However, the second approach would not (assuming at least one e-mail application was installed & associated with the "mailto:" protocol).

Overlooking this restriction, the CreateObject approach is more flexible as you can specify far more parameters (properties) compared to the hyperlink (as only "subject", "to", "cc", "bcc", & "body" parameters are supported in the query string).  There is also a maximum number of characters able to be passed via a URL/query string parameters, & the creation of HTML-enabled e-mail body content needs to be 'escaped' (converted to ASCII representations of Unicode characters; "RFC5137").  This is not the case using the Document Object Model syntax of MS-Outllook.

When executing the CreateObject approach you will have more control over the reporting of runtime errors & failures to create/send an e-mail.  The "mailto: " approach either works, or it doesn't... & you may never know if it has worked successfully as once the command has been passed to the e-mail application you have no idea if the e-mail was created &/or sent.

BFN,

fp.

PS. Ron de Bruin's "Excel Automation" site contains some variations on sending e-mail content:

[ http://www.rondebruin.nl/win/section1.htm ].

You may also wish to look at the use of "Collaboration Data Objects" [CDO] (previously called "OLE Messaging" or "Active Messaging"):

[ http://www.rondebruin.nl/win/s1/cdo.htm ].
0
 

Author Comment

by:David Phelops
ID: 39656062
Thank you fp for this comprehensive (and fast!) response.
I'm slowly digesting the information.

I had a look at the RFC5137 - OW, that is one document too far for me!!

I have a had a peek at Ron de Bruin's site - thanks for that signposting.

Cheers
David
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39656067
You're very welcome.

Sorry, yes, the "RFC5137" document is perhaps too much information... just here for those looking for keywords in the site's search engine in the future.

Happy reading! :)
0
 

Author Closing Comment

by:David Phelops
ID: 39656083
The post answered my questions precisely, concisely and was intelligent and friendly in tone. The signpost to further reading helped as well.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39656150
:)

Thanks for accepting the answer & for not mentioning my spelling of "MS-Outllook" (with double 'l').
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question