Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Code to change default email account (Word 2000 / VBA)

    I am creating a Word MailMerge to Email program using VBA controls. I have the main core set up (With ActiveDocument.Mailmerge....) but before the merge is initiated I want to change the email account to be used when the merge creates the outgoing messages, and then restore the default account once finished. Is there a VB/VBA command or similar that can read the computer's default email account name, change the account, and then restore the original? Or is there a registry key that can be changed using the Windows Script 'RegWrite' method to accomplish this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code to change default email account (Word 2000 / VBA)

    The default profile name is stored in HKEY_CURRENT_USERSoftwareMicrosoftWindowsNTCurrent VersionWindows Messaging SubsystemProfilesDefaultProfile.

    In Outlook, you can use the Logon method of the NameSpace object to log on under a specific profile. The following is the example in the Outlook VBA help:

    Set myOlApp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlApp.GetNameSpace("MAPI")
    myNameSpace.Logon "myProfile", "myPassword", True, True

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 2000 / VBA)

    Thanks. Found a workable solution myself though, using WshShell.RegWrite. I had tried using this previously without success - I could manipulate a registry key but Outlook wouldn't recognize the change. Today I found the key to the puzzle: close Outlook before changing the registry keys, and when Outlook is re-opened the changed values are recognized.

    The mostly-finished code (I'd love a variable subject line, using data pulled from the mailmerge source file):

    Code:
    Dim WshShell
    
    Sub macStartMerge()
        
    On Error GoTo Errors
    
    
    'Create the Shell object
        Set WshShell = CreateObject("WScript.Shell")
    
    
    'Close Outlook before manipulating the system default email account
            Shell "C:Program FilesAccessoriesTaskill.exe outlook.exe"
    
    
    'Change the default email account to the desired sending account
        WshShell.RegWrite "HKCUSoftwareMicrosoftOfficeOutlookOMI Account Manager" _
            & "Default Mail Account", "00000002", "REG_SZ"
    
    
    'Run the mail merge
        With ActiveDocument.MailMerge
            .Destination = wdSendToEmail
            .MailAsAttachment = False
            .MailAddressFieldName = "EMAIL"
            .MailSubject = "Subject"
            .SuppressBlankLines = False
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=True
        End With
    
    
    'Restore the default email account
        WshShell.RegWrite "HKCUSoftwareMicrosoftOfficeOutlookOMI Account Manager" _
            & "Default Mail Account", "00000001", "REG_SZ"
    
    
    'Open Outlook to send the messages
        Shell "C:Program FilesMicrosoft OfficeOfficeoutlook.exe"
    
    
    'Everything complete, finish program
        GoTo Done
    
    
    Errors:
    
    'Display a message box with the error encountered
        MsgBox ("The program encountered the following error:" & Chr(13) & Chr(10) & _
            Chr(10) & Err.Description)
        Exit Sub
    
    
    Done:
    
    End Sub

    Taskill is a stand-alone program that can end a running process. http://www.ozemail.com.au/~nulifetv/freezip/freeware
    Last edited by macropod; 2013-12-03 at 04:27. Reason: Added code tags

  4. #4
    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: Code to change default email account (Word 200

    Is this running in Word? If so, you might be able to change the registry key using the System.PrivateProfileString property. Cribbing from the help file a bit:

    Code:
    varOldValue = System.PrivateProfileString("", _
    	"HKEY_CURRENT_USERSoftwareMicrosoft" _
    	& "WindowsCurrentVersionInternet Settings", "EmailName")
    System.PrivateProfileString("", _
    	"HKEY_CURRENT_USERSoftwareMicrosoft" _
    	& "WindowsCurrentVersionInternet Settings", "EmailName") = varNewValue
    One less external library to invoke.
    Last edited by macropod; 2013-12-03 at 04:28. Reason: Added code tags

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 200

    Unfortunately, I have to admit that the code is not working as intended. I was hoping that the MailMerge-generated messages would use the changed default email account as the sending account, but they actually used the default account at time they were being sent. The default account was being changed to the alternate account as intended and the messages generated, but by the time they were being sent the default had been changed back -- and that was the account being used when Outlook's 'send' took effect. One, maybe two, messages went out using the changed account, but the rest go out with the 'restored' default account.

    Word doesn't support a 'Wait' command, but Excel's VBA library does (and the 'Help' file gives an excellent example of how to use this code) so I added Excel's library to the standard 'Word' library list and was able to use it to build in a delay before executing the code to restore the original default account. What would have been a 10-second 'Wait' delay in Excel ran much longer in Word and still left open the possibility for Outlook to begin sending with the wrong account.

    It looks like I may have to use MailMerge to generate the messages and then find a way to determine when Outlook's 'Outbox' folder empties before restoring the default account back to the original value (ie, If count of outgoing messages = 0 then continue with change to default email account).

    UNLESS -- Would your code example allow the created outgoing messages to use the changed email account as the outgoing account regardless of the default account set at the time the messages are actually sent?

    It might help to clarify why these changes are necessary. The MailMerge will be processed and sent out from our IT department but the replies need to be received through a different account. The recipients of the outgoing messages need to be able to 'Reply' and have their message go to the desired email account (100% chance the reply would be sent to the correct receiving account, no margin for error allowable). This could easily be accomplished by using a separate PC to create the outgoing messages but there's no way to reasonably justify the cost of setting up a single-purpose PC just for what is expected to be a temporary (weeks/months vs years) situation.

  6. #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: Code to change default email account (Word 200

    The properties I listed do not change your overall process at all.

    Is this a frequent task? Would it be too much to ask the user to manually change the default back after completing the merge/send process?

    How about setting a Reply To address? Actually, it doesn't appear that the MailMerge object provides access to this option. Because it's a property of the default "form" for email messages, I don't suggest making a global change.

    Does the Application_ItemSend event fire when you send messages using mail merge? If so, you could rig a procedure to recognize your merged messages (perhaps by a repetitive subject line?), and switch the account on the fly at the time of sending. Perhaps similar to the thread Prompt for Account (2003), although maybe that won't work because there is no visible message compose window...

    Well, after all that, I endorse the "two profiles" method described above.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 200

    Is this a frequent task? Would it be too much to ask the user to manually change the default back after completing the merge/send process?
    This program would be run every couple of days, in a busy computer room with a history of not following procedures explicitly -- past experience tells me that anything more complicated than a button-click leaves the process open to too many human-induced errors.

    Perhaps an application-modal messagebox to halt the code's execution immediately after completing the merge: "Merge complete. Now go to Outlook and make sure the messages get sent before you click this 'OK' button." If they actually wait until the outbox is empty before switching back to Word and clicking the messagebox button, that might be good enough to do the trick. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Last edited by macropod; 2013-12-03 at 04:29. Reason: Added quote tags

  8. #8
    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: Code to change default email account (Word 200

    I'm not sure you need user intervention. Let's say you close Outlook and either change the default account and restart without specifying a profile, or restart with an alternate profile locked to the "special" account. You run the merge and Outlook sends. Your procedure in Word watches for all of the messages in the Outbox to be gone. Then it close Outlook, switches the account back and restarts, or restarts with the default profile.

    The "trick," such as it is, is to take control over the Outlook session you start. If you use the automation code in Hans' post, then you have an application object reference you can use to examine the contents of the Outbox. Use the API function Sleep to space your Outbox checks to decent intervals (e.g., every 2000 milliseconds) and then close Outlook once sending is complete (e.g., using the .Quit method of your Outlook application object).

    It might be a good idea to display a non-modal userform that says "Please Wait, Generating Messages" so the user doesn't try to slip around and play with Outlook while you are sending...

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 200

    What, precisely, do you mean by loading a profile for Outlook? All of our installations are set up as Internet Mode Only, and I've never seen an option for selecting a profile to use.

    I would like to have a procedure in Word to watch Outlook's Outbox and pause Word until the outgoing message count = 0 and thereby eliminate the need for an artificial pause, but for now I don't know how to access the folder's information.

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Code to change default email account (Word 200

    Outlook Properties (or Control Panel > Mail) will take you to this dialog box.

    StuartR

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 200

    What I get from Control Panel > Mail is the dialog box for Internet Accounts.

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Code to change default email account (Word 200

    I'm running Outllook 2003, I guess you must be running Outlook Express?

    StuartR

  13. #13
    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: Code to change default email account (Word 200

    Sounds as though you will have to continue using the Registry key change option. Or upgrade to a version of Outlook that is less than 5 years old. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  14. #14
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Code to change default email account (Word 200

    Hmmmm..... The <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> solution of having an extra button-click in the program versus <img src=/S/money.gif border=0 alt=money width=17 height=15> <img src=/S/money.gif border=0 alt=money width=17 height=15> <img src=/S/money.gif border=0 alt=money width=17 height=15> to update a lot of computers and train-up? Which way would you go?

    Maybe when we upgrade the PC's to Vista... <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    Anyhow, I'm going to consider my initial questions solved. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> I've got code that works (maybe not the best but good enough nonetheless), and I've learned a good deal in the process.

  15. #15
    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: Code to change default email account (Word 200

    Here's an example that puts together the two ideas I described above: using Word's System.PrivateProfileString method to change the registry, and automating the shut down and restart of Outlook and monitoring of the Outbox using VBA. I can't test this very well on my system, as I don't have the merge document and I don't have a default account. Perhaps, though, it will help you somehow.

    The attached is an exported .BAS module, renamed to a .txt extension. To import it into your template or document, rename it to .BAS and use the VBE's File>Import feature.

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
  •