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:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What happens for the 365 users?
 

mharrington

New Member
Joined
Oct 7, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Does it end up in the outbox? It sounds like a configuration issue to me.
 

mharrington

New Member
Joined
Oct 7, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,651
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, it just uses the default.

Do the 365 users already have Outlook running?
 

mharrington

New Member
Joined
Oct 7, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,066
Messages
5,545,792
Members
410,707
Latest member
BarnOwl
Top