Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 949
  • Last Modified:

VB.net Excel Add-In Sending Outlook Email when Outlook is closed

Hi

I am using the following code in my VB.net Excel add-in to send an email. It works just fine
if Outlook is open but crashes when Outlook is closed. How can I modify the code to send it even if Outlook is close?

Imports Microsoft.Office.Interop.Outlook

Module modEmail

    Public Sub oSendEmail(ByVal oTo As String, ByVal oCc As String, ByVal oSubject As String, ByVal oBody As String, ByVal oAttachment As String)

        Try


            ' Create an Outlook application.
            Dim oOutApp As Microsoft.Office.Interop.Outlook.Application
            oOutApp = New Microsoft.Office.Interop.Outlook.Application

            ' Create a new MailItem.
            Dim oMsg As Microsoft.Office.Interop.Outlook.MailItem
            oMsg = oOutApp.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem)

            oMsg.Subject = oSubject
            oMsg.Body = oBody

            ' TODO: Replace with a valid e-mail address.
            oMsg.To = oTo
            oMsg.CC = oCc

            ' Add an attachment
            Dim sSource As String = oAttachment 'eg "C:\Temp\Hello.txt"

            'Dim sBodyLen As String = oMsg.Body.Length
            Dim sBodyLen As Integer = oBody.Length


            Dim oAttachs As Microsoft.Office.Interop.Outlook.Attachments
            Dim oAttach As Microsoft.Office.Interop.Outlook.Attachment
            'only attach if there is something there
            If oAttachment <> "" And oAttachment <> Nothing Then
                oAttachs = oMsg.Attachments
                If oAttachment <> Nothing And oAttachment <> "" And System.IO.File.Exists(oAttachment) = True Then
                    oAttach = oAttachs.Add(sSource, , sBodyLen + 1)
                    'oAttach = oAttachs.Add(sSource, , sBodyLen + 1, sDisplayName)
                End If
            End If

            ' Send
            oMsg.Send()
            ' Clean up
            oOutApp = Nothing
            oMsg = Nothing
            oAttach = Nothing
            oAttachs = Nothing
        Catch
            MsgBox("An error occurred in trying to send an email! " & Err.Description)
        End Try

    End Sub

End Module
0
Murray Brown
Asked:
Murray Brown
  • 4
  • 3
1 Solution
 
CodeCruiserCommented:
This code should work given that you have

oOutApp = New Microsoft.Office.Interop.Outlook.Application

What is the error you get?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. I included oOutApp = New Microsoft.Office.Interop.Outlook.Application
but still get the error as shown in the attached image
1
0
 
CodeCruiserCommented:
Do you know which line is generating the error? Does outlook start? Before running this account when outlook is closed, is the outlook.exe in processes list in task manager?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi

The error occurs at the line  oMsg.Send.
Nothing visible happens. Outlook doesn't start.
outlook.exe is not  in processes list in task manager

Thanks
0
 
CodeCruiserCommented:
Well the

oOutApp = New Microsoft.Office.Interop.Outlook.Application

line should start Outlook.

See if this trick works for you

http://stackoverflow.com/questions/11330101/can-only-send-email-via-outlook-if-outlook-is-open
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Here's the working code for anyone that views this:

    Public Sub oSendEmail(ByVal oTo As String, ByVal oCc As String, ByVal oSubject As String, ByVal oBody As String, ByVal oAttachment As String)

        Try


            ' Create an Outlook application.
            Dim oOutApp As New Microsoft.Office.Interop.Outlook.Application
            oOutApp = New Microsoft.Office.Interop.Outlook.Application

            Dim ns As Microsoft.Office.Interop.Outlook.NameSpace = oOutApp.GetNamespace("MAPI")

            Dim f As Microsoft.Office.Interop.Outlook.MAPIFolder
            f = ns.GetDefaultFolder(OlDefaultFolders.olFolderInbox)

            System.Threading.Thread.Sleep(3000) 'a bit of startup grace time.



            ' Create a new MailItem.
            Dim oMsg As Microsoft.Office.Interop.Outlook.MailItem
            oMsg = oOutApp.CreateItem(Microsoft.Office.Interop.Outlook.OlItemType.olMailItem)

            oMsg.Subject = oSubject
            oMsg.Body = oBody

            ' TODO: Replace with a valid e-mail address.
            oMsg.To = oTo
            oMsg.CC = oCc

            ' Add an attachment
            Dim sSource As String = oAttachment 'eg "C:\Temp\Hello.txt"

            'Dim sBodyLen As String = oMsg.Body.Length
            Dim sBodyLen As Integer = oBody.Length


            Dim oAttachs As Microsoft.Office.Interop.Outlook.Attachments
            Dim oAttach As Microsoft.Office.Interop.Outlook.Attachment
            'only attach if there is something there
            If oAttachment <> "" And oAttachment <> Nothing Then
                oAttachs = oMsg.Attachments
                If oAttachment <> Nothing And oAttachment <> "" And System.IO.File.Exists(oAttachment) = True Then
                    oAttach = oAttachs.Add(sSource, , sBodyLen + 1)
                    'oAttach = oAttachs.Add(sSource, , sBodyLen + 1, sDisplayName)
                End If
            End If

            ' Send
            oMsg.Send()
            ' Clean up
            oOutApp = Nothing
            oMsg = Nothing
            oAttach = Nothing
            oAttachs = Nothing
        Catch
            MsgBox("An error occurred in trying to send an email! " & Err.Description)
        End Try

    End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now