Solved

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

Posted on 2013-01-17
7
881 Views
Last Modified: 2013-01-21
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
Comment
Question by:murbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38789603
This code should work given that you have

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

What is the error you get?
0
 

Author Comment

by:murbro
ID: 38791866
Hi. I included oOutApp = New Microsoft.Office.Interop.Outlook.Application
but still get the error as shown in the attached image
1
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38792277
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
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!

 

Author Comment

by:murbro
ID: 38800668
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 38801041
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
 

Author Closing Comment

by:murbro
ID: 38801255
Thanks very much
0
 

Author Comment

by:murbro
ID: 38801428
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question