#1
|
|||
|
|||
Email from Command Button
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! Last edited by macropod; 05-03-2016 at 06:17 PM. Reason: Added code tags |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
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. |
#4
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
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 Last edited by macropod; 05-04-2016 at 08:36 AM. Reason: Deleted unnecessay quote of entire post replied to. |
#6
|
||||
|
||||
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.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
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 |