Macro to email sheet not working in Outlook 2007

**drewship** · 02-12-2010, 10:28 AM

Hello all. I have the following code that works for Outlook 2003 but does not work for Outlook 2007. Does anyone know what changes need to be made so this macro will work in both Outlook versions?

Please Login or Register  to view this content.

Thanks,
Andrew

**DonkeyOte** · 02-12-2010, 11:58 AM

Works for me on 2007 ... do you have a C:\temp folder on your machine ?

**drewship** · 02-12-2010, 12:23 PM

Hello DonkeyOte. When we were first upgraded to Vista/Office 2007 I noticed that I no longer had access to the C:\ drive so changed the code to place the file in the C:\Temp. In the code you can still see where I commented out the original directory location. What used to happen is that the macro would run, I would get a popup asking if I wanted to allow an external program to open Outlook, I clicked yes, Outlook opened and the email was sent. Now, there is no indication that an email is sent but the code does tell me it has completed. I look at my sent items and the C:\Temp folder but there is nothing there.

I have attached a test workbook. Just cancel out of the external database requests. All data necessary has been hard entered. I am using my laptop with XP and Outlook 2003. When I click on the email button (K1) on the All_Report, the above actions occur, and I have opened my C:\Temp folder and watched the file save and then be deleted. Additionally, I can see the emails in the Outbox before they are sent. I will not have access to Outlook 2007 until Tues when I go back to the office, but when I tried the same actions over the past several days at work, I see no indication of an email.

Thanks,
Andrew

**DonkeyOte** · 02-12-2010, 12:31 PM

I just tested again and all was ok... I can't recall having to change the Trust Settings in OL at all...
(ie High Security in OL won't make a difference given the VBA is not in OL)

If no one else resolves in the meantime I'd suggest stepping through with F8 and see what happens step by step...
looking at the code it should do "something" even it's a debug or mailing a mail item with no attachment...

**drewship** · 02-12-2010, 12:48 PM

Thanks DonkeyOte. I will step through Tues with the outbok and C:\Temp open to see if the file and/or email are created. I will post back with my results.

Andrew

**drewship** · 02-16-2010, 02:57 PM

Just finished stepping through the code and I saw the file being saved to the C:\Temp folder but Outlook never requested permission to send an email or did anything to indicate that the code had tried to open it.

I will keep looking. It may be that there is some setting in Outlook 2007 that is preventing this but I have not found anything yet.

Thanks,
Andrew

**buran** · 02-16-2010, 03:47 PM

Hi,
Just tested it. Here it works without any problem.

Buran

**DonkeyOte** · 02-17-2010, 04:59 AM

Originally Posted by Andrew

Just finished stepping through the code and I saw the file being saved to the C:\Temp folder but Outlook never requested permission to send an email or did anything to indicate that the code had tried to open it.

Andrew, I'd suggest disabling the On Error Resume Next statement prior to the With OutMail line (by commenting it) ... then step through again and see if an error is raised at some point within that section. Let us know.

We're most probably all missing something obvious in terms of config... I can't recall setting anything in OL 2007 to permit automation but it was a while ago.... I'll ask some others.

**romperstomper** · 02-17-2010, 07:09 AM

None of the Programmatic Access options should stop it working - they just determine whether you get warned or not.
First places to start would be:
1. Change the email address to send to yourself and see if you receive it.
2. Change the .Send line to .Display to see if the email is created properly
3. As DO said, remove the On Error Resume Next line.

As an aside, I don't really get the method of copying the workbook, killing and closing it. I think it would be better to use SaveCopyAs, attach that copy and then kill that copy rather than trying to kill the open workbook before closing it.

**drewship** · 02-17-2010, 10:28 AM

Thanks romperstomper, " Change the .Send line to .Display to see if the email is created properly" did the trick!! It not only let me see the email but I was able to send it. I also made the changes you recommended. Everything is tested and working great. Thanks everyone!!

Andrew