Automate emailing current workbook to end users

[Robert_Conklin]

I currently have a workbook that employs userforms that my end users fill out to send in part maintenance requests. Once we receive the workbooks from the end users I process their requests into out SAP system. Once I process the data, I have to click the email icon in my quick reference bar to email the open workbook back to the end user.

Is it possible to use VBA to trigger an automatic email sent back to the end user with the open workbook as an attachment?

 

[NoSparks]

Maybe something along the lines of this will assemble the email addresses...

Sub Mail_workbook_Outlook()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim SendTo As String
    Dim fndPlant As Range
    Dim firstAddress As String
    
    With Sheets("Lists").Range("L:L")
        Set fndPlant = .Find(What:=Me.T_02.Value, _
                             LookIn:=xlValues, _
                             LookAt:=xlWhole, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext, _
                             MatchCase:=False)
        If Not fndPlant Is Nothing Then
            firstAddress = fndPlant.Address
            Do
                SendTo = SendTo & "," & fndPlant.Offset(, 2).Value
                Set fndPlant = .FindNext(fndPlant)
            Loop While Not fndPlant Is Nothing And fndPlant.Address <> firstAddress
        End If
    End With
    
    'remove leading comma from SendTo string
    SendTo = Mid(SendTo, 2)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
    .to = SendTo                '<-- you can add email addresses separated by comma
    .CC = "SPAR@flocorp.com"
    .BCC = ""
    .Subject = "Spare Parts Maintenance"
    .Body = "The parts have been placed on today's load sheet and will be processed by EOB today.  This data has also been placed on the repository file."
    .Attachments.Add (Application.ActiveWorkbook.FullName)
    .Display                            '<-- .Display will show the email first for review
    '.Send                              '<-- .Send will auto send email without review
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[Robert_Conklin]

I get an error message that stated:

Compile error - invalid use of Me keyword

I thought that the field name T_02 needed to be bracketed, but I still received the same error message.

 

[Logit]

.
The email macro : Mail_workbook_Outlook (because of the line "What:=Me.T_02.Value") requires it be pasted in the User Form. The term ME refers to the user form and
the term T_02.Value refers to the textbox on the user form.

Where did you paste the macro ?

 

[NoSparks]

Or...
if for some reason you don't want or can't have that particular user form loaded when running the email macro,
you could use the afterupdate or exit event of T_02 to write its value to a cell on a sheet, then reference the cell value.

 

[Robert_Conklin]

I just replaced the code that was in the module. I gathered from what you said above, that the code needed to be set in the user form. I did so, but when I ran the code, it did not produce an email. The code referenced in post #3 worked great, but it did not fill in the To: section of the email. Which is what I am trying to accomplish.

 

[Robert_Conklin]

Any direction would be greatly appreciated.

 

[Logit]

.
Bob

The quickest means to determine the issue is to post your workbook on a 'cloud' site. Lots of folks use DROPBOX.COM. Its free. Or there are others free as well: Google, Amazon, etc.

I'll take a look when the workbook is available for download.

Of course ....don't include anything in the project that is confidential (real email addresses, phone numbers, etc.)

 

[Robert_Conklin]

Thank you, I will let you know when it is available.