Macro email button issue - opening additional Excel "shells" when pressed

[mattm11]

Hi all,

I am having some trouble with a Macro Button Issue...when it is pressed the macro has an email come up -- everything in regards to the email is perfect. However, at the same time, two additional Excel windows appear on my task bar. They Excel windows are shells and do not have a spreadsheet in them. Please help.

Also, the email automatically comes up with a closing prompt: "Want to save your changes? Yes No Cancel"

Excel 2013
Windows 8.1

Sub EmailfromExcel()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("C2")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
 
  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = ""  ' <-- Put email of the recipient here
    .CC = "[EMAIL="mmcmullen@landrys.com"]mmcmullen@landrys.com[/EMAIL]" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Will you Teach Me [insert subject here] within the next 2 weeks? Please email me your availability and I will be happy to adjust my schedule to meet yours." & vbLf & vbLf _
          & "Regards," & vbLf _
          & "[insert your name & store here]" & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "Please make sure you are signed in to your Landry's Outlook email account. Then please go back and press the Email Instructor button again", vbExclamation
    Else
      MsgBox "Please fill in the [insert here] areas of the email", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[Darren Bartrup]

I ran this on Excel 2010 & XP and it didn't produce any more versions of Excel.

I did notice that you're using GetObject & CreateObject for Outlook - I remember reading somewhere that there can only be a single instance of Outlook so if you try to use CreateObject and Outlook is already open then it just references that instead - no need to use GetObject.

Also: Title = Range("C2") will take the value of C2 on the active sheet, which may not be the required sheet.
Better to use Title = ThisWorkbook.Worksheets("Sheet1").Range("C2")

 

[mattm11]

Thank you, Darren Bartrup. I'll take a look at it this weekend. I may ask for you to explain further. But I'll take a look first.