Email from Command Button

derajlance · #1 05-03-2016, 03:04 PM

I am sure this has been asked before, but I can't seem to find a solution that is actually working. I am very new at VBA, so I apologize in advance.

I have a Word document with drop down lists, text boxes, and a command button. My ultimate goal is to allow my employees to fill out the drop down lists and text boxes, then have them click the command button and the information be sent to me in the body of an email.

I need it to follow the same

I have tried multiple times with information I found from Google, but nothing seems to be working. At this moment, I am using the following:

Code:

Private Sub CommandButton1_Click()
    ActiveDocument.Content.Copy
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objInspector As Outlook.Inspector
    Dim objDoc As Word.Document
    Set objOutlook = CreateObject("Outlook.Application")
 
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
   .To = "recipient@domain.com"
    .Subject = "Report"
    .Body = ActiveDocument.Content
    .Send
    End With
    Set objInspector = objOutlook.ActiveInspector
    If Not objInspector Is Nothing And objInspector.EditorType = olEditorWord Then
        Set objDoc = objInspector.WordEditor
        objDoc.Range.Paste
    End If
    Set objDoc = Nothing
    Set objOutlookMsg = Nothing
    Set objInspector = Nothing
    Set objOutlook = Nothing
End Sub

The problem seems to be with the code:
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objInspector As Outlook.Inspector
Dim objDoc As Word.Document

As well as:
.Body = ActiveDocument.Content

I have unprotected the file and attached it here.
Any help would be greatly appreciated!

macropod · #2 05-03-2016, 06:21 PM

I haven't tested your code, but what it's missing is a reference to the Microsoft Outlook # Object Library (where # is the version #), which you insert in the VBE via Tools|References.

derajlance · #3 05-03-2016, 08:20 PM

Quote:

Originally Posted by macropod

I haven't tested your code, but what it's missing is a reference to the Microsoft Outlook # Object Library (where # is the version #), which you insert in the VBE via Tools|References.

Thanks for such a quick response! I will see if I can figure that out when I get to work tomorrow.
Most of the Visual Basic stuff I have done has been copy, paste, and fill in the blanks. I thought I was getting the hang of it and figuring out some of it myself, until I started banging my head against the wall because of this.
I will update tomorrow and mark as resolved if I can figure out how to edit what I need to edit.

gmayor · #4 05-03-2016, 09:43 PM

Unfortunately, there is far more wrong with the code than an omission of a reference to Outlook, which in any case is not entirely necessary, depending on how you define the various Outlook objects. At a cursory glance the most obvious problem is that you are trying to paste to the message body after you have sent it?

The following code does not require the reference to Outlook and will paste the document body to the message body.

Unfortunately that too will not achieved the desired aim, because the fields you propose to insert into the message body, to take user input, are not compatible with the message format and so will not work when the user receives the message.

You have two choices:
1. Don't use fields in the document. A two column table with space for the responses in the second column may work for you.
2. Use fields but send the form as an attachment. You don't need the document inspector to do that, unless you want to add a covering message - see the second code example.

In both code examples, the message is displayed momentarily. Do not remove the .Display command or the message body will not be edited.

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objInspector As Object
Dim objDoc As Word.Document
Dim objRange As Range

    ActiveDocument.Content.Copy
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        MsgBox "Outlook is not running."
        'While you can create an Outlook object in code, " & _
        "the message cannot be edited reliably using the Outlook Inspector when you do so, " & _
        "so it is better to start Outlook first."
        GoTo lbl_Exit
    End If
    On Error GoTo 0

    Set objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
        .to = "recipient@domain.com"
        .Subject = "Report"
        Set objInspector = .GetInspector
        Set objDoc = objInspector.WordEditor
        Set objRange = objDoc.Range(0, 0)
        .Display
        objRange.Paste
        .send
    End With
lbl_Exit:
    Set objDoc = Nothing
    Set objRange = Nothing
    Set objOutlookMsg = Nothing
    Set objInspector = Nothing
    Set objOutlook = Nothing
    Exit Sub
End Sub

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objInspector As Object
Dim objDoc As Word.Document
Dim objRange As Range
Dim sDocname As String
    ActiveDocument.Save
    sDocname = ActiveDocument.FullName
    If Len(ActiveDocument.Path) = 0 Then
        MsgBox "Document is not saved!"
        GoTo lbl_Exit
    End If
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        MsgBox "Outlook is not running."
        'While you can create an Outlook object in code, " & _
         "the message cannot be edited reliably using the Outlook Inspector when you do so, " & _
         "so it is better to start Outlook first."
        GoTo lbl_Exit
    End If
    On Error GoTo 0

    Set objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
        .to = "recipient@domain.com"
        .Subject = "Report"
        .attachments.Add sDocname
        Set objInspector = .GetInspector
        Set objDoc = objInspector.WordEditor
        Set objRange = objDoc.Range(0, 0)
        .Display
        objRange.Text = "Complete the attached report and return to the sender."
        .send
    End With
lbl_Exit:
    Set objDoc = Nothing
    Set objRange = Nothing
    Set objOutlookMsg = Nothing
    Set objInspector = Nothing
    Set objOutlook = Nothing
    Exit Sub
End Sub

derajlance · #5 05-04-2016, 08:19 AM

Thank you! Slightly disappointed to learn that the text from the fields can't just be copied and pasted, but that's life.

The first option you presented won't work because I need the fields to ensure consistency in the formatting. Without those fields, they don't seem to grasp how to format the email I need.

Re: the second option to send as an attachment, I see that it requires the document to be saved.

Code:

ActiveDocument.Save
    sDocname = ActiveDocument.FullName
    If Len(ActiveDocument.Path) = 0 Then
        MsgBox "Document is not saved!"
        GoTo lbl_Exit

Is there a way to remove that and make it automatically save in a temporary folder upon clicking submit and it be deleted after sending? I hope that makes sense.

gmayor · #6 05-05-2016, 12:31 AM

I think we may be slightly at cross purposes. The first of the macros I posted will send the completed form data as the body of the message. What it won't do is retain that data as the fields they were in the original document. If you are supplying the document to users with the macro for them to complete send the results back to you, then include the first macro in the document.

derajlance · #7 05-05-2016, 07:40 AM

Obviously I was not understanding what you were saying when you provided the code. That was exactly what I needed! My headache is now over.
Thank you so much!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Submit to Email Command Button	rob7676	Word VBA	0	08-20-2015 05:05 AM
Help with a Command Button	dkohnken	Word VBA	5	03-18-2015 10:13 PM
command button that generates a popup for sending email	oduntan	Word VBA	4	03-21-2013 02:15 PM
Command button - save in temp folder and send email with attachment	bigbird69	Word VBA	13	11-18-2012 10:06 PM
Command Button	cksm4	Word VBA	7	02-27-2011 08:47 PM