Rob90
08-28-2022, 11:49 AM
Hi Everyone,
I am writing some macros for my department as we need to automate some emails. I have created the code and the email works as intended, the problem is the attachment. I am using the latest excel version, and the VBA code is below:
Sub SendEmail_EmpRef()
Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application
Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)
EmailItem.To = Sheet7.Range("A6")
EmailItem.Subject = Sheet7.Range("A5")
EmailItem.Body = Sheet7.Range("A7")
Source = ThisWorkbook.FullName
EmailItem.Attachments.Add "C:\Users\myuser\Desktop\T1_New Starter Reference Request Form.docx"
EmailItem.Display
End Sub
The issue is the part in bold. This will only work if I am the one executing the macro, while all my colleagues need to be able to execute it and get the email with the attachment. I have tried this by quoting the SharePoint link, but it would attached a file with the error message "unable to download", so the code still works. However, using SharePoint is just one solution and the requirement is really that all my colleagues can execute this and get the same result.
Any idea on how to make sure any user executing this macro would still see the required attachment in the email?
Many thanks for your kind help!
This is my first post, so sorry if I forgot to mention any important details :)
Roberto
I am writing some macros for my department as we need to automate some emails. I have created the code and the email works as intended, the problem is the attachment. I am using the latest excel version, and the VBA code is below:
Sub SendEmail_EmpRef()
Dim EmailApp As Outlook.Application
Dim Source As String
Set EmailApp = New Outlook.Application
Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)
EmailItem.To = Sheet7.Range("A6")
EmailItem.Subject = Sheet7.Range("A5")
EmailItem.Body = Sheet7.Range("A7")
Source = ThisWorkbook.FullName
EmailItem.Attachments.Add "C:\Users\myuser\Desktop\T1_New Starter Reference Request Form.docx"
EmailItem.Display
End Sub
The issue is the part in bold. This will only work if I am the one executing the macro, while all my colleagues need to be able to execute it and get the email with the attachment. I have tried this by quoting the SharePoint link, but it would attached a file with the error message "unable to download", so the code still works. However, using SharePoint is just one solution and the requirement is really that all my colleagues can execute this and get the same result.
Any idea on how to make sure any user executing this macro would still see the required attachment in the email?
Many thanks for your kind help!
This is my first post, so sorry if I forgot to mention any important details :)
Roberto