Forum Discussion
Sending Outlook mail using Excel VBA
I'm familiar with that excellent site, but it does not address the issue I'm having. (Or at least I can't find it.) I don't care if the macro fails when Outlook is not open. The folks using the macro use Outlook all day long.
Wayne-
Sorry I think I misunderstood your issue. I haven't fiddled around with Outlook VBA in a while, but maybe try changing this line:
Set objInsp = objMail.GetInspector.CurrentItem
If that doesn't work try these two modifications:
Add this to your Dim Statements:
Dim outApp As Outlook.Application
Then change this code as well:
Set objInsp = outApp.ActiveExplorer.CurrentItem
If neither of these will work. Please let me know and I can do additional testing to try and replicate your issue.
Sorry about the delay. I work on a Mac but this code has to be tested on a Windows machine.
Anyway, the .CurrentItem attempt returned a "type Mismatch" error. My tester reports that .CurrentView was on the list of suggestions but .CurrentItem was not.
The second attempt, the Dim statement and the objInspector line returned an error "object variable or with block variable not set"
Wayne-
No problem. I'll run some additional tests. However, before I do, I wanted to ask a few questions
I see MS Word VBA references in your code which makes me wonder:
1. If possible would you like to copy data from Excel directly to the email?
2. Are you trying to copy a table from word to the email?
3. Is word being used as some type of intermediary? If so what is the purpose?
The only reason I ask about these items is because there is a way to copy directly from excel and maintain formatting, but if you are trying to copy the table from word then the code will be different of course....
Nuts. I had a more detailed answer but I put a link in it and that got it removed as spam. I've asked for it to be restored.
Anyway,
1. Yes
2. Only as a way to get the Excel selection into an email with format retained.
3. Yes, an intermediate editor step ONLY, so that the Excel formatting is not lost. Apparently Outlook VB cannot do that.
[update] We're getting closer. We used SendKeys to tab forward into the body while we were setting the .Subject of the objMail. (Using .Body to add text causes a loss of formatting.) With this change, the macro works all the time EXCEPT when the last created email is open. I find this bizarre but by trial and error we discovered that closing or sending the most-recent email in Outlook allows the macro to succeed even if older Outlook mail windows remain open.
I tried a kludge to open and then close a dummy email within the macro, before creating the desired email. This sadly didn't work. There's something crucial about actually looking at the email in Outlook and then closing it.