Consulting

Results 1 to 1 of 1

Thread: Create email with attachment from sharepoint

  1. #1
    VBAX Newbie
    Joined
    Aug 2022
    Posts
    1
    Location

    Question Create email with attachment from sharepoint

    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
    Last edited by Aussiebear; 08-28-2022 at 04:45 PM. Reason: Reduced whitespace in Post and added code tags to supplied code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •