Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating an e-mail message from Word VBA (VBA Word 2000)

    Sorry to post so many messages in succession. You guys are great to help out so much. Let me know if I'm taking advantage.

    I'd like to open a new e-mail message window from Word via VBA. I then need to populate the To and Subject boxes and send.

    If it would make it easier, we can assume Outlook is the e-mail client. That will be true most of the time, but there may be exceptions.

    Thanks again for the help!!
    Troy

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Troy, you have used up your requests for the month. And it's only the 4th! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    To automate Outlook from another Office application, you have a lot of power but a few issues.

    First, here are some code samples:

    Sample #1
    Sample #2

    Second, there are macro security issues that will arise on machines that have certain security settings. For example, users might get a dialog asking them to approve the access. This is discussed regularly on the Outlook board, and maybe here as well, so you could search for more info.

    Third, if a computer has multiple profiles set up in Corporate Workgroup mode, and no default, you might have to logon:

    Sample #3

    Hope this helps.

  4. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Thanks for the code samples. I could probably get one of these to work.

    HOWEVER, it seems that all of them require the reference to be set to msoutl9.olb. I've searched through a previous posting on how to set this programmatically. Though it is a little scary, I'm willing to give it a try. However, what I could not seem to find is how to find the GUID or whatever would be necessary to set this reference programmatically.

    I can almost get things to work like I want to with:

    ActiveDocument.FollowHyperlink Address:="mailto:email.address@mailbox.com", NewWindow:=True

    I just don't know how to add a subject and send this. I'm thinking it could be done, but I just don't know how.

    If you could help with either of these I'd appreciate it!!
    Troy

  5. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Troy,

    The earlier thread you're likely referring to ("Adding a reference to an object library in code") appears to contain the solution to this question - first set the reference manually, and then run the code in Andrew Cronnolly's <post#=100288>post 100288</post#> to determine the GUID.

    Gary

  6. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Thanks Gary! I think I just forgot how to use this info (not to mention that's a long, but good, post to wade through to find the final outcome.) Now I got it.

    Troy

  7. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    You could just use CreateObject("Outlook.Application") as that should be universal across all versions of Outlook.

    Arguably late binding isn't as efficient for the end user. Compared with debugging the alternatives, it is much more efficient for the programmer. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    <P ID="edit" class=small>Edited by TroyWells on 06-Aug-02 15:10.</P>Alright. Everything is working great IF Outlook is open. However, on PCs when Outlook is closed, I get the following error when connnecting to Outlook via VPN:

    Run-time error '-1940897787 (8c504005)':

    Cannot create the e-mail message because a location to send and receive messages could not be found. To add a location, click the Tools menu, click Services, and then click the Delivery tab.


    This is happening at the following line of code:

    Set msgItem = olApp.CreateItem(CurrentItem)

    EDIT START********************************************* ********
    On other PCs connected directly to the network, I get the following error:

    Run-time error '-2079063791 (84140111):

    The server is unavailable. Contact the system administrator if this condition persists.


    This is happening at the following line of code:

    olNameSpace.Logon ProfileName, PasswordIfAny, blnShowDialog, False

    EDIT END*********************************************** *********

    The following is the code in the procedure (btw this is a compilation of parts and pieces from the code samples you gave):

    Declarations

    Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" ( _
    pOpenfilename As OpenFileName) As Long

    Private Declare Function GetActiveWindow Lib "user32" () As Long

    Private Declare Function GetFileTitle Lib "comdlg32.dll" Alias "GetFileTitleA" ( _
    ByVal lpszFile As String, _
    ByVal lpszTitle As String, _
    ByVal cbBuf As Integer) As Integer

    Private Type OpenFileName
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
    End Type

    Public CanceledSelection As Boolean

    Public GlobalTemplatePath As String
    Public FileNameAndPath As String
    Public FileNameOnly As String
    Public DOTversion As String

    Procedure

    Public Sub RegisterTemplate()
    Dim olApp As Outlook.Application, olNameSpace As Outlook.NameSpace
    Dim msgItem As Outlook.MailItem, msgRecip As Recipient

    'Ask the user if they want to register.
    If MsgBox("Do you want to register the installation of this template?" & vbCrLf & vbCrLf & _
    "If you register, it allows us to contact you when more features or bug fixes are available for this template file. We will not use your e-mail address for any other purpose." & vbCrLf & vbCrLf & _
    "NOTE: If you click Yes, you will asked to allow this program to send the e-mail. Click Yes when prompted.", vbQuestion + vbYesNo + vbDefaultButton1) = vbNo Then
    Exit Sub
    End If
    'Register the user.
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then 'Outlook was not open...
    Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    Set olNameSpace = olApp.GetNamespace("MAPI")
    olNameSpace.Logon ProfileName, PasswordIfAny, blnShowDialog, False
    Set msgItem = olApp.CreateItem(CurrentItem)
    'Set msgItem = olApp.ActiveInspector.CurrentItem
    With msgItem
    .To = "emailaddress@mailserver.com"
    .Subject = "Register: " & FileNameOnly & " - " & DOTversion
    .Send
    End With
    Set msgRecip = Nothing
    Set msgItem = Nothing
    Set olApp = Nothing

    End Sub

    Let me know your thoughts. Thanks again!!
    Troy

  9. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Troy,

    I'll jump in here with a bit of advice. Jefferson mentioned the drawbacks to using the Outlook object model to send email from a vb or vba (or any client) app. Those are indeed drawbacks that frustrated me until I explored the alternatives. I suggest you do the same in order to get around the error you post here and others that are sure to crop up in the future.. If your app will run at a site with Exchange Server, you can use the SMTP engine on it (using the CDONTS objects) or as others have done, use the SMTP engine called "Redemption" (I think that's the name. Charlotte knows.) There are other object libraries available too, for free. Post back if you are interested in a free one.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Kevin, I'm interested in knowing where to get a free object library for email from vba ... especially if you can vouch for its usefulness.
    Gwenda

  11. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Tell me more about this CDONTS. I have to distribute this procedure to multiple PCs, all running Office 2000. I'd rather set a reference that would be the same for all these machines than have to distribute an extra file that I would have to explain and hope it is used correctly. Is CDONTS another library included with Office 2000? I couldn't find it by that name.

    I actually don't mind setting the reference at this point, since this seems to work well if they have Outlook running. If I have to, I'll put an error handler in place to tell them to start over after they get Outlook running. That seems the only drawback right now to what I have.

    Thanks!!
    Troy

  12. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Let's start with this one:
    <UL><font color=blue>Run-time error '-2079063791 (84140111):
    The server is unavailable. Contact the system administrator if this condition persists.

  13. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Redemption isn't an SMTP library. It uses Extended MAPI rather than CDO and the regular MAPI calls. Redemption is not free, but it isn't expensive either, and it is redistributable.
    Charlotte

  14. #13
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    477
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    OK, you caught me sleeping. <img src=/S/snore.gif border=0 alt=snore width=32 height=15> I obviously didn't look close enough to see that those were variables. Your workaround code worked great, EXCEPT it weirded me out. Why? Because I now saw a prompt for the profile, and the mail message was sent, but I never saw Outlook open. I have Windows 2000. I could see Outlook.exe in the Processes tab while the message was being created and then sent, but did NOT see Outlook in the Applications tab. After the e-mail was sent, Outlook.exe disappeared from the Processes tab. Actually, I think that is great, but I'm wondering two things:

    1. Was this really what was supposed to happen?

    2. Given what happens, do you think Joe Blow user would be wierded out by not seeing Outlook open? If so, how would I open it and keep it open?

    I didn't get a chance to check to see if this fixed the VPN issues yet. I'll let you know. However, I was curious what you meant by "start Outlook the old-fashioned way, using the Shell, and then using GetObject again. " I've never used "the Shell", but I'm assuming you mean the Shell function. If I did this, is there an automated way to find the path to Outlook before I have to pass that to the function?

    Thanks again for all the help!!
    Troy

  15. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    You see. I knew you knew. Thanks for the concise explanation and clarification.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  16. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating an e-mail message from Word VBA (VBA Word 2000)

    Check out wsInetTools V.3B at http://www.winscripter.com. I've not used it, but know the author who vouche for it's effectiveness. I use the CDONTS technique for now.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Page 1 of 2 12 LastLast

Posting Permissions

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