Mail Merge with BCC or equivalent?

[baconaise]

Greetings,

My job often requires me to use Microsoft Outlook to send the same email to a long list of addresses while BCCing a different address for recordkeeping. It takes a long time to do this manually, and I'd like to automate it.

Microsoft Word's Mail Merge seemed like a clear choice for sending the emails as it lets me send the same email to a list of addresses contained in an Excel spreadsheet, but it does not appear to allow BCCing. I was hopeful that I could set up a rule for Outlook that would automatically BCC, but it only appears to offer CC (not BCC).

I've been poking through the OutlookEX UDF because I figured I could loop through the addresses and BCC without issue. Try as I can, I seem unable to use this UDF to send an email I've constructed in Outlook. I can send messages typed into a GUICtrlCreateInput, but I can't seem to figure out a way to send a premade email with embedded pictures and the like. The closest I managed to get was emailing C:\Location\Of\The\File\On\My\Drive.msg.

Do any of you see a clean way to do this?

[Luke94]

You could use CDO.Message:

expandcollapse popup

#include <StringConstants.au3>

Global $g_sUserName = 'youremailaddress'
Global $g_sPassword = 'yourpassword'

_SendEmail('from@outlook.com', 'to@outlook.com', 'tobcc@outlook.com', 'Hello <b>baconaise</b>!')

Func _SendEmail($sFrom, $sTo, $sToBCC, $sHTMLBody)
    Local $oError = ObjEvent('AutoIt.Error', '_AutoItError')
    Local $oMessage = ObjCreate('CDO.Message')
    If @error Then Return SetError(1)
    With $oMessage
        .From = $sFrom
        .To = $sTo
        .BCC = $sToBCC
        .HTMLBody = $sHTMLBody
        With $oMessage.Configuration.Fields
            .Item('http://schemas.microsoft.com/cdo/configuration/sendpassword') = $g_sPassword
            .Item('http://schemas.microsoft.com/cdo/configuration/sendusername') = $g_sUserName
            .Item('http://schemas.microsoft.com/cdo/configuration/sendusing') = 2
            .Item('http://schemas.microsoft.com/cdo/configuration/smtpauthenticate') = 1
            .Item('http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout') = 10
            .Item('http://schemas.microsoft.com/cdo/configuration/smtpserver') = 'smtp-mail.outlook.com'
            .Item('http://schemas.microsoft.com/cdo/configuration/smtpserverport') = 25
            .Item('http://schemas.microsoft.com/cdo/configuration/smtpusessl') = 1
            .Update
        EndWith
        .Send
        If @error Then Return SetError(2)
    EndWith
EndFunc

Func _AutoItError($a)
    ConsoleWrite(StringFormat('ERROR: Description: %s', StringStripWS($a.Description, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpContext: %s', $a.HelpContext) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpFile: %s', $a.HelpFile) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: LastDllError: %s', $a.LastDllError) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Number: 0x%s', Hex($a.Number)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: RetCode: 0x%s', Hex($a.RetCode)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: ScriptLine: %s', $a.ScriptLine) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Source: %s', $a.Source) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: WinDescription: %s', StringStripWS($a.WinDescription, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
EndFunc

If I'm sending a large email I sometimes create the email and save it as a HTML file. Then read the HTML file within the script and pass it into the $sHTMLBody parameter. As for sending to multiple addresses, simply delimit each address with a semi-colon.

_SendEmail('from@outlook.com', 'to@outlook.com;to2@outlook.com;to3@outlook.com', 'tobcc@outlook.com', 'Hello <b>baconaise</b>!')

You may need to check the SMTP server and port are correct.