0

I have an excel doc that people in my department use to send mass emails. To monitor who is emailed monthly, I want to have it set up to email me a copy of the file with data without the user having to save a copy of the file. Currently, I am emailed the excel doc with no data on it. I tried to record a macro using the share feature in Excel. When I do this, macro just says

Application.Dialogs(xlDialogSendMail).Show

This shows the email along with the file with data attached. Now would like to autopopulate my email information and then have the file with data emailed to me. Does anyone know how to do that with VBA? BTW, I have to use late binding for this process. My current code that sends a copy of the file without data is below.

    'Create an object for Outlook
    Set OutlookApp = CreateObject("Outlook.Application")
    'Create an object for the email
    Set OutgoingEmail = OutlookApp.CreateItem(0)

    'Turns off error handling (stops the "someone is trying to send an email" message)
        On Error Resume Next

With OutgoingEmail
        .to = "MyWorkEmail@Workemail.com"
        '.CC =
        '.BCC = ""
        .Subject = NumberofPPL & " " & StaffBadge & " Mass Email Attempt"
        .Body = NumberofPPL
        .Attachments.Add (file)
        .Send
    End With
    
    Set OutgoingEmail = Nothing
    Set OutlookApp = Nothing
MsAgentM
  • 153
  • 2
  • 14
  • Maybe its me, but can't seem to get what you're after? Your code above should send an email using the users outlook application without confirmation? If you need a record, you could create a hidden worksheet (suggest using xlVeryHidden) where you simply write a line with x info on executed macro. – Havard Kleven May 31 '22 at 18:36
  • I would like to automate sending or sharing a file in excel by using the "Share" option in the File menu. That way I get the file with the data the user added. The code above works, I just get the file without any user data added. – MsAgentM May 31 '22 at 18:53
  • You could save the file as an attachment on OneDrive, and then upload that file as attachment to the mail object. – Havard Kleven May 31 '22 at 19:03
  • Ok, is there a place I can find the vba code to do that? The "Application.Dialogs(xlDialogSendMail).Show" bit of code provides the email. I'm not sure if I can set the outlook object to that email somehow or if I need to take an approach like you recommend, but I can't find any out to's on how to do something like this with VBA. – MsAgentM May 31 '22 at 19:10
  • I don’t believe you need that first part - your mail is already being sent using Mailobject.Send. Take a look here on how to save to OneDrive: https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive – Havard Kleven May 31 '22 at 21:45

0 Answers0