none
VB Script to Send Email via Outlook RRS feed

  • Question

  • Good afternoon, all -

    I have a VBA script that runs as an Excel macro to send an email to a distribution list. this is working fine and I was wanting to convert a copy of it for use in a VB Script task in SSIS.

    I thought I'd be able to simply copy the script over and have no problems, but, apparently, I shouldn't think.

    I've been looking up scripts to do the task online, but the ones I find don't seem to be at all similar to what I'm already using in my macro. I guess this is not exactly unexpected, but I figured VB is VB, whether it's Excel or SSIS. But, maybe not.

    Anyway, the code I'm using is;

    '*****  Set date values.
    
        Dim sYear As String = Year(Now)
        Dim sMonth As String = Format(Month(Now), "00")
        Dim sDay As String = Format(Day(Now), "00")
    
    '*****  Define the distribution list
        
        Dim 'Recipient_1 As String = "John.Doe@domain.org"
        Dim 'Recipient_2 As String = "Herman.Munster@domain.org"
        Dim 'DL_1 As String = "Big.Group@domain.org"
    
    '*****  Create the email
    
        Set oOutApp = New Outlook.Application
        Set oOutMail = oOutApp.CreateItem(0)
        Set objDoc = oOutMail.GetInspector.WordEditor
        Set objBkm = objDoc.Bookmarks("_MailAutoSig")
    
    '*****  Get Outlook Account Number
    
        Dim OutApp As Outlook.Application
    
        Set OutApp = CreateObject("Outlook.Application")
    
        For I = 1 To OutApp.Session.Accounts.count
            If Right(OutApp.Session.Accounts.Item(I), 21) = "@specific_account.org" Then Acct_Num = I
        Next I
        'MsgBox OutApp.Session.Accounts.Item(Acct_Num)
    
    '*****  Create the email subject line
    
        sSubj = "Test Email"
    
    '*****  Create the message for the email body
    
        sMsg = "Test message."
        
        On Error Resume Next
    
    '*****  Populate the email fields and send it
    
        With oOutMail
            Set .SendUsingAccount = oOutApp.Session.Accounts.Item(Acct_Num)
            .Recipients.Add (Recipient_1)
            .Recipients.Add (Recipient_2)
            .Recipients.Add (DL_1)
            .Subject = sSubj
            .HTMLBody = sMsg & .HTMLBody
            .Attachments.Add ("C:\temp\Chart1.png")
            .Send
            '.Display
        End With
        On Error GoTo 0
        
        Set oOutMail = Nothing
        Set oOutApp = Nothing

    I have the Get Outlook Account Number in there, because we recently changed our corporate email addresses, but still maintain the old address for the foreseeable future. However, we need to ensure that all outgoing emails use the new addresses. So, I have that test to get the right account.

    SSIS' VB Script Task didn't like the date values as I had them, so I had to add the "Dim … As String …" etc. in the Set date values section at the top. Originally, they were just sYear = Year(Now), etc. Same goes for the strings in the Define the distribution list section.

    The VB in SSIS also seems to gag on the Set command. Seems to want it removed. But, "Set .SendUsingAccount = oOutApp.Session.Accounts.Item(Acct_Num)" in the Populate the email fields and send it section requires it in Excel. The specific account will not be selected, if that line is there without the Set.

    So, apparently, this code will not work as is. Does anyone have a simple little email routine that will let me send emails using Outlook and that allows me to select a specific account?

    Thanx in advance for any assistance!

    Tuesday, September 17, 2019 7:29 PM

Answers

All replies

  • Hi Adam Quark,

    The Outlook is usually not installed on the servers. That's why it is better to use the System.Net.Mail namespace.

    Here is a good link on the subject: Sending mail within SSIS - Part 2: Script Task

    • Marked as answer by Adam Quark Tuesday, September 24, 2019 4:05 PM
    Tuesday, September 17, 2019 7:37 PM
  • Hi Adam Quark,

    The Outlook is usually not installed on the servers. That's why it is better to use the System.Net.Mail namespace.

    Here is a good link on the subject: Sending mail within SSIS - Part 2: Script Task

    That link looks informative. I'll have to give it a good read.

    Thanx, Yitzhak!

    Tuesday, September 17, 2019 7:47 PM
  • Hi Adam,

    Check if these tutorials are helpful.

    There is also a VB Script Code.txt provided in the Resources.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 18, 2019 1:35 AM
  • Hi Adam Quark,

    What's the latest?

    Did the proposed solution help you to resolve the issue?

    Wednesday, September 18, 2019 8:17 PM
  • Hi Adam Quark,

    What's the latest?

    Did the proposed solution help you to resolve the issue?

    So far, still struggling. Seems the IT folks may force us to use O365 cloud and take away our desktop versions. I'm wondering if that's the cause for the issues I'm having.

    But, I'm still squeezing in opportunities to go over the provided links, though it seems I may have a permissions problem. At least, one of the errors that hit me said that the server refused my request. So, I've got a help ticket sent off for that.

    We are due to get new machines - though, not for a year, they say - and, on these boxes, we are supposed to have complete Admin control. So, that may solve some problems, too.

    Meanwhile, thanx to you and Zoe for your assistance!

    Wednesday, September 18, 2019 8:24 PM
  • Hi Adam Quark,

    The SSIS Script Task solution based on the System.Net.Mail namespace. should work without any problem with O365 cloud.

    Please find below settings that worked for me. Please ignore the XML format.

    • <add key="smtpAddress" value="smtp.office365.com"/>
    • <add key="portNumber" value="587"/>
    • <add key="enableSSL" value="true"/>
    • <add key="emailFrom" value="yitzhak.khabinsky@domainName.com"/>
    • <add key="password" value="ItIsaBigSecret"/>
    • Marked as answer by Adam Quark Tuesday, September 24, 2019 4:05 PM
    Wednesday, September 18, 2019 8:32 PM