Email Workbook macro not working with Windows 7

**Lewej23** · 07-19-2012, 02:38 PM

I have the following code:

Sub EmailWorkbookToCoreAccounts()
    
    xlsmname = Range("A2").Value
    Edate = Format(Now, "MM-DD-YYYY hhmm")
    ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\All Users\Desktop\" & xlsmname & "_" & Format(Edate, "MM-DD-YYYY hhmm") & ".xlsm", FileFormat:=52, CreateBackup:=False
    
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = xlsmname
        .Body = ""
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

The code first saves the workbook to the user's desktop then starts an email (via Outlook) with the workbook attached. It seems that this code works perfectly with Windows XP, but doesn't work with Windows 7 - any ideas on how to fix this?

Edit Post 7-19-12 3:19pm:
I should mention that in Windows 7 the macro will save the workbook to the desktop, start an email, but it doesn't attach the workbook to the email.

**Medpack** · 07-19-2012, 04:06 PM

Windows 7 has a ton of security related preventions. Not sure if this will solve it, but have you tried running the process as admin?

**Lewej23** · 07-19-2012, 04:18 PM

I'm not sure if I'm understanding you right - but are you saying to log out of the computer then log back in as admin and then trying it?
If you are, unfortunately that's not possible - the computers we use are owned by a company and only our Help Desk has admin capabilities...

**Medpack** · 07-19-2012, 06:18 PM

Oh ok, so the macro actually initially runs, but has an issue attaching to the email? Did you also happen to upgrade versions for Excel/Outlook? There are some modifications you may have to make to your code between 2010 and older versions of code.

**Lewej23** · 07-20-2012, 11:08 AM

The person that's been testing it in Windwos 7 has Office 2010. I also have Office 2010, but I'm working in Windows XP.

**Lewej23** · 08-03-2012, 01:47 PM

This problem still persists... anyone have any solutions???

**Lewej23** · 08-22-2012, 06:52 PM

Ok everyone.... after 2 months of trying to figure this out, I finally got it! Here's the code I'm using if anyone needs it:

Sub EmailWorkbook()

xlsmname = Range("A2").Value
Edate = Format(Now, "MM-DD-YYYY hhmm")
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\All Users\Desktop\" & xlsmname & "_" & Format(Edate, "MM-DD-YYYY hhmm") & ".xlsm", FileFormat:=52, CreateBackup:=False
    
Application.Dialogs(xlDialogSendMail).Show arg1:="[email protected]", arg2:=xlsmname
    
End Sub

I've tested this in both Windows XP/Office 2010 and Windows 7/Office 2010 and both have worked. I'm assuming this will work with Office 2007, but I haven't had a chance to test it out, yet.