Hello Community After upgrading to Office 365 attached macro does not work anymo

Question

Hello Community

After upgrading to Office365 attached macro  does not work anymore. When reaching to the syntax : " Set olNewMail = CreateItem(olMailItem)"

it stops for almost 60 seconds before it continues, but no mail is sent.

Any good ideas ?

Thanks in advance

Regards Kim Koch

Sub SendEmail()

Sheets("Tabel").Select

On Error Resume Next

    Dim olApp As Outlook.Application

    Dim olNewMail As Object

    Dim Recep As String

    Dim MsgTxt As String

    Dim Attchedfile As String

    Dim Sti As String

    Dim Varnavn As String

    Dim Vedhaeft As String

    Dim antallinier As String

    Sti = "" & Sti1 & ""

    Filende = ".xls"

    ' Finder antal linier som skal t

Explanation / Answer

In your code you used GetObject to grab an existing instance of the Outlook application. If Outlook wasn't running that would fail.

For most applications, you can do this, e.g. for Word:

Sub CreateWordApplication()

    Dim bWeStartedWord As Boolean
    Dim wdApp As Object              'If using Late Binding
'    Dim wdApp As Word.Application   'If using Early Binding, and have set a reference to
                                     'Microsoft Word Object Library via VBE > Tools > References..
    
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    If wdApp Is Nothing Then
       Set wdApp = CreateObject("Word.Application")
        bWeStartedWord = True
    End If
    
    'If you want to make Word visible:
    wdApp.Visible = True
   
    'Do stuff with Word here...
   
    'Tidy up:
    If bWeStartedWord Then wdApp.Quit
    Set wdApp = Nothing
End Sub

Note how:

1) We disable error handling

2) We try to get an existing instance of Word using GetObject

3) We test to see that wdApp is not Nothing

4) If it is nothing, we use CreateObject to start Word

5) We can use wdApp.Visible to make the application visible to the user

6) We make a note that we started Word

7) At the end of the macro, if we started Word, we Quit it

8) Make sure you set the application object to be = Nothing at the end.

Now, with Outlook, it's not quite as simple, you can't use olApp.Visible. Instead you can do this:

Sub CreateOutlookApplication()
    Dim bWeStartedOutlook As Boolean
    Dim olApp As Object              'If using Late Binding
'    Dim olApp As Word.Application   'If using Early Binding, and have set a reference to
                                     'Microsoft Outlook Object Library via VBE > Tools > References...
    
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        bWeStartedOutlook = True
    End If
   
    'If you want to make Outlook visible:
    olApp.Session.GetDefaultFolder(6).Display     '6=olFolderInbox
   
    'Do stuff with Outlook here...
   
    'Tidy up:
    If bWeStartedOutlook Then olApp.Quit
    Set olApp = Nothing
End Sub

But be careful, with Outlook it's important to give it time to connect to the server and send any messages before quitting. It is probably better to leave it open, in which case you really ought to leave it visible (otherwise the only way to kill it would be via task manager, Processes tab).

But like I said at the top, to check that the previous code I gave you works ok, simply start Outlook yourself first, then run the code in Excel.

Hope that helps.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.