Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-27-2015, 06:54 AM
fusion001 fusion001 is offline Create bookmark and use it in macrobutton Windows 7 64bit Create bookmark and use it in macrobutton Office 2010 64bit
Novice
Create bookmark and use it in macrobutton
 
Join Date: May 2015
Posts: 3
fusion001 is on a distinguished road
Default Create bookmark and use it in macrobutton

Hi...I have a word document which has a table on it which a user needs to complete. One of the fields on the table is "Contact Email" where the person compelting the form inserts their email address.
Once the form is completed it is emailed to someone else to add their bits and then the form is supposed to be sent back to the original person.

I would like to automate the part of the form being sent back to the original person. I would like to know how to bookmark the "Contact Email" field and then use this in the macrobutton to run with my code to send the email with the attachment.

The macro button code will be like { macrobutton sendemail emailaddress }

I would like to know how to create the bookmark and then be able to add it to the macrobutton automatically as the email address will change each time. Or if there is a better way of doing this.

I hope this makes sense. Thanks for any help.
Reply With Quote
  #2  
Old 05-28-2015, 01:13 AM
gmayor's Avatar
gmayor gmayor is offline Create bookmark and use it in macrobutton Windows 7 64bit Create bookmark and use it in macrobutton Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

Your message says 'field'! What kind of field?

In practice you can read the cell content directly without using a bookmark e.g. the following will read cell Row 1, Column 1 of Table 1 and use that as the message recipient. The code also assumes Outlook is available.

The code goes in an ordinary module in the document itself which should be saved as macro enabled. - http://www.gmayor.com/installing_macro.htm

Code:
Option Explicit

Sub SendMessage()
Dim olApp As Object
Dim olEmail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Range

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo err_Handler
    Set olEmail = olApp.CreateItem(0)
    With olEmail
        .BodyFormat = 2
        .to = GetCell(ActiveDocument.Tables(1), 1, 1)        'Last two digits are row and column
        .Subject = "This is the message subject"
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range(0, 0)
        oRng.Text = "This is the message body text" & vbCr & _
                    "This is another line of message body."
        .Display
        '.Send 'remove apostrophe from the start of the line after testing
    End With
lbl_Exit:
    Set olApp = Nothing
    Set olEmail = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
err_Handler:
    MsgBox "There has been an uncorrected error -" & vbCr & vbCr & "Number: " & _
           Err.Number & vbCr & "Description: " & Err.Description
    GoTo lbl_Exit
End Sub

Public Function GetCell(oTable As Table, _
                        iRow As Long, _
                        iCol As Long) As String
Dim oCell As Range
Dim strText As String
    Set oCell = oTable.Cell(iRow, iCol).Range
    oCell.End = oCell.End - 1
    GetCell = oCell.Text
    MsgBox GetCell
lbl_Exit:
    Exit Function
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 05-28-2015, 01:24 AM
fusion001 fusion001 is offline Create bookmark and use it in macrobutton Windows 7 64bit Create bookmark and use it in macrobutton Office 2010 64bit
Novice
Create bookmark and use it in macrobutton
 
Join Date: May 2015
Posts: 3
fusion001 is on a distinguished road
Default Code

Hi Thanks for the reply.

Sorry what I mwan by "field" is that the word document has a table with columns and rows.

On the form I also have fixed email addresses which I have assigned a macro button to send the email with the attachment. But the person who originally completes the form will be different each time so I'm not sure how to get the email address to populate on the TO address with the attachment.
I'm attaching a sample file to illustrate what I mean. Thanks
Attached Files
File Type: docx form[1].docx (13.5 KB, 7 views)
Reply With Quote
  #4  
Old 05-28-2015, 02:00 AM
fusion001 fusion001 is offline Create bookmark and use it in macrobutton Windows 7 64bit Create bookmark and use it in macrobutton Office 2010 64bit
Novice
Create bookmark and use it in macrobutton
 
Join Date: May 2015
Posts: 3
fusion001 is on a distinguished road
Default Code

The above code works brilliantly...I just had to add a line to add the attachment to the email. Thanks so much
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create bookmark and use it in macrobutton Bookmark Not Showing Bookmark RegAudit Word 6 03-16-2015 11:08 PM
Problem using MacroButton Nomacro cebjlca Word 2 10-28-2014 11:26 AM
Create bookmark and use it in macrobutton MacroButton as image RonNCmale Word 3 01-16-2014 08:51 AM
Create bookmark and use it in macrobutton Macrobutton shifts view when document is protected for forms Cosmo Word 7 10-15-2013 03:28 PM
MacroButton MednataMiza Word 0 06-29-2010 01:59 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft