Creating macro in excel 2016 workbook to share via Outlook 365

mharrington

New Member
Joined
Oct 7, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hey there - I work for a company that utilizes both Microsoft 2016 and O365 programs. Many users only have access to Outlook 365 and not the 2016 version.

What I'm trying to do, is create a button in excel that will automatically attach the workbook to an email. I have the following set up for Outlook 2016 users and it works great. But I'm struggling on how to duplicate this so that it composes the email in outlook 365. Any suggestions?
VBA Code:
Sub SendWorkBook()
'Update 20131209
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = Range("AA23")
    .CC = ""
    .BCC = ""
    .Subject = "Report Name Here"
    .Body = "Hello, please see the attached report."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
What happens for the 365 users?
 
Upvote 0
What happens for the 365 users?
A compose-new email window pops up with the workbook attached, the user sends the email, but it never goes through. They look through their 365 sent box, and it doesn't exist. Users who have access to both 365 and 2016 found that it will send once they open their 2016 outlook. But this is an issue for users who do not have 2016 outlook as an option.
 
Upvote 0
Does it end up in the outbox? It sounds like a configuration issue to me.
 
Upvote 0
Does it end up in the outbox? It sounds like a configuration issue to me.
In the O365 email, no, it does not. Looked in the sent, outbox, and drafts and it doesn't exist.
Is there a setting within excel that allows you to modify what email account you share from when using the "send as attachment" option?
 
Upvote 0
No, it just uses the default.

Do the 365 users already have Outlook running?
 
Upvote 0
No, it just uses the default.

Do the 365 users already have Outlook running?
Bummer - this is going to create some issues with our processes.
Yes, they typically have their email open already when the macro is ran. But we've tried doing it with and without their emails open- same issue. The only time it works as it should is if they have access to Outlook 2016 and have it open when they run the macro.

We have the workbook saved to sharepoint where everyone has access to the document. When they select the document, it opens in "excel desktop" because the in-browser version does not support the macros correctly.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top