Outlook treats first email created by Excel differently

**blackworx** · 09-14-2011, 05:38 AM

Hi folks,

I have some VBA code in Excel which loops through a list of email addresses, creates an email to each one and saves the emails in the Outlook drafts folder.

If Outlook is already running when my code starts then everything works as planned and all the emails are saved to Outlook's drafts folder.

However, if Outlook is not already running when my code starts then the FIRST email always appears in the INBOX, and not always straight away. e.g. If I run my code without Outlook loaded and immediately start up Outlook afterwards, then all the emails except the first one are present in the Drafts folder; then, about a minute or so later, Outlook's Inbox will update to show "1 unread message" which is always the first email in the list. Clicking Outlook's send/recieve button does nothing to speed up the appearance of this rogue first email.

I have worked around this odd behaviour by asking the user to start up Outlook if it is not already running, but I was just wondering whether any of you have experienced this before, or if anyone has any ideas for a less intrusive solution?

Cheers

EDIT: Forgot to say I already tried creating a "dummy" first email and just deleting it instead of saving it to the Drafts folder, but that made no difference. It seems to be the first ".Save" method on an Outlook.MailItem that causes this behaviour. Invoking the .Save method then immediately attempting to delete the saved message won't work either, because it is not in the Drafts folder, nor will it appear in the Inbox until many seconds later.

**snb** · 09-14-2011, 06:52 AM

Please post your VBA code so we can see which method you use to connect to Outlook.

**blackworx** · 09-14-2011, 06:56 AM

Hi snb thanks for your quick reply.

Code below. It's a sub from a userForm - can post the entire frx if you want.

There are a few global variables/data types used that are not declared in the snippet but it should all be fairly clear.

Private Sub btnGo_Click()

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Dim shtDL As Worksheet 'distribution list
Dim rCur As Long 'current row number
Dim rMax As Long 'last row number of dist list

Dim pwCur As Integer 'current progress bar width
Dim pwPrv As Integer 'progress bar width at last update

Dim validationResult As validationReport

Dim attPath As String 'path for individual attachment files

'reset progress bar
    lblComplete.Visible = False
    lblProgress.Width = 0

'warn if sending live emails
    If Not chkDraft Then
        If MsgBox("If Outlook is currently online then this will instantly send emails to everyone in the selected distribution list.  Continue?", _
                  vbOKCancel + vbExclamation + vbDefaultButton2, "Emailer") = vbCancel Then Exit Sub
    End If
    
'set reference to user's chosen distribution list
    Set shtDL = ThisWorkbook.Sheets(cbxDistList.Value)
    
'detect last data row in shtDL
    rMax = shtDL.Cells(65536, 1).End(xlUp).Row
    
'validate shtDL
    validationResult = listValidate(shtDL, rMax)
    
    If validationResult.badRowCount > 0 Then
    
        With shtDL
            .Activate
            .Cells(rMax, 1).Select
            .Cells(validationResult.firstBadRow, 1).Select
        End With
        
        If MsgBox(validationResult.badRowCount & " invalid row" & IIf(validationResult.badRowCount = 1, "", "s") & _
                  " found in distribution list.  Proceed anyway and skip " & IIf(validationResult.badRowCount = 1, "it?", "them?"), _
                  vbOKCancel + vbExclamation + vbDefaultButton2, "Emailer") = vbCancel Then Exit Sub
                  
    End If
        
'read HTML body text
    On Error Resume Next
    
    Set hb = fs.openTextFile(pthH & cbxHTMLsrc)
    
    HTMLbody = hb.ReadAll
    
    hb.Close
    Set hb = Nothing
    
    If Err.Number Then MsgBox "Error opening " & cbxHTMLsrc & vbCrLf & Err.Description, vbCritical, "Emailer": GoTo localErr
    
'generate emails
    On Error GoTo 0
    
    Set objOutlook = CreateObject("Outlook.Application")
    
    objOutlook.Session.Logon
    
    If optEmlIndivs Then 'user specified send as individual emails
    
        'initialise progress bar
        pwPrv = -1
        pwCur = 0
        lblProgress.Visible = True
        
        'loop through distribution list
        For rCur = 2 To rMax
        
            'update progress bar
                pwCur = Int((rCur / rMax) * pwMax)
                
                If pwCur > pwPrv + 10 Then 'increment in large steps to prevent userform flickering
                
                    pwPrv = pwCur
                    lblProgress.Width = pwCur
                    Me.Repaint
                    
                End If
            
            'create email; ignore rows explicity marked as to be skipped or invalid (listValidate filled them with orange)
            If LCase(shtDL.Cells(rCur, 3)) <> "yes" And shtDL.Rows(rCur).Interior.ColorIndex = xlNone Then
                
                'create email object
                Set objEmail = objOutlook.CreateItem(0)
                
                'add any specified attachments
                addAttachments objEmail
                
                With objEmail
                
                    .To = shtDL.Cells(rCur, 1)
                    
                    .Subject = Replace(txtSubject, "[NAME]", shtDL.Cells(rCur, 2))
                    
                    .HTMLbody = Replace(HTMLbody, "[NAME]", shtDL.Cells(rCur, 2))
                    
                    If chkDraft Then
                        .Save
                        .Close False
                    Else
                        .Send
                    End If
                    
                End With
                
            End If
            
        Next rCur
        
    Else 'send as group email
    
        'write me
        
    End If
    
    objOutlook.Session.Logoff
    
    Set objOutlook = Nothing
    
    lblProgress.Width = pwMax
    lblComplete.Caption = """" & cbxDistList & """ processed successfully"
    lblComplete.Visible = True
    
localErr:
Err.Clear
End Sub

**snb** · 09-14-2011, 07:57 AM

I think this would do

sub snb()
 sn=ThisWorkbook.Sheets(cbxDistList.Value).cells(1).currentregion
 
 with Createobject("Outlook.application")
  for j=1 to ubound(sn)
   if sn(j,3)<>"yes" then
    with createitem(0)
     .to=sn(j,1)
     .subject=sn(j,2)
     .HTMLbody=sn(j,3)
     .save
    end with
   end if
  next
 end with
end sub

The faster the code the less you need a progressBar.

I think the problem lies here:

If chkDraft Then
  .Save
  .Close False
 Else
  .Send
 End If

You don't need this check as you can see in my code.

**blackworx** · 09-14-2011, 08:20 AM

Thanks. Duly noted on the speed comment - I never use .Currentregion - more through ancient habit than choice and old habits die hard, especially when they are bad ones!

Either way the progress bar is there only for pretties, as well as to notify completion without resorting to a modal dialogue, not because the code is taking too long.

The problem can't lie with the check itself (which I do actually need - chkDraft is a local checkbox control with which the user specifies whether or not to save the emails as drafts or just send them - something I neglected to mention in my OP). However I noticed that you haven't used a .Close immediately following your .Save. Removing that solved the problem.

Thanks again for your time and assistance

**blackworx** · 09-14-2011, 09:05 AM

Actually I remembered why I don't as a rule use currentregion -- resilience against user error, in case they decide to remove entries from a list by simply clearing rows instead of deleting them.

**snb** · 09-14-2011, 09:22 AM

That's what userforms are for: preventing users to work in worksheets.

**blackworx** · 09-14-2011, 09:29 AM

Not entirely sure what you mean by that. Personally I can't see the point of creating a whole front end for the management of some simple lists when I can code around the most predictable problems caused by giving users worksheet access very easily.

If this program was meant for anything more than a small group of users then perhaps I could see the point of doing that, but then if that was the case then I wouldn't be using Excel+VBA for the job in the first place. This is just a simple quick and dirty distribution list program with a deliberately narrow set of features.