Using Access VBA to Prefill Word Document Bookmarks then send on outlook. (1 Viewer)

totalnovice2 · May 21, 2013

Hi.

I have been working on a project for 3 months now and i am really close to completion but this last but is a real sticking point.

As you'll see in the code below i have the code to fill in bookmarks on word using data from my access database, the "activedocument.sendmail" line only allows me to open an instance of Outlook but no further details.

I need to be able to add code to the below to tell it that the .subject should be the information from the access fields (named below), the .body should be some predetermind text and there needs to be a pre-determined e-mail address too.

I have looked around online but i can't seem to find anything telling me exactly what to do.

Any help would be great thanks.

Code:

Private Sub cmdo2permit_Click()
On Error Resume Next

'Declare the follwing
Dim objWord As Word.Application
'Set word as an application and make it invisible
Set objWord = CreateObject("Word.Application")
objWord.Visible = False 'True is visible

'path and name of the template your are using.
objWord.Documents.Add ("C:\Users\602180742\Desktop\Auto\Access\New Access\Latest\O2Maygyrney.dotm")

'This is for the bookmark that you created in the template
objWord.ActiveDocument.Bookmarks("O2today").Select

'This is the field in access that containts the data that has to be entered at the
'bookmark
objWord.Selection.Text = Forms![Front Page]![Auto_Date]

objWord.ActiveDocument.Bookmarks("O2CS").Select
objWord.Selection.Text = Forms![Front Page]![Site 2 Name]

objWord.ActiveDocument.Bookmarks("O2CSNAME").Select

objWord.Selection.Text = Forms![Front Page]![Site 2 Owner]

objWord.ActiveDocument.Bookmarks("O2CSPOSTCODE").Select
objWord.Selection.Text = Forms![Front Page]![Postcode S2]

objWord.ActiveDocument.Bookmarks("O2ENG1").Select
objWord.Selection.Text = Forms![Front Page]![Combo79]

objWord.ActiveDocument.Bookmarks("O2ENG2").Select
objWord.Selection.Text = Forms![Front Page]![Combo81]

objWord.ActiveDocument.Bookmarks("O2ENG3").Select
objWord.Selection.Text = Forms![Front Page]![Combo83]

objWord.ActiveDocument.Bookmarks("O2ENG4").Select
objWord.Selection.Text = Forms![Front Page]![Combo93]

objWord.ActiveDocument.Bookmarks("O2ENGLEAD").Select
objWord.Selection.Text = Forms![Front Page]![Combo79]

objWord.ActiveDocument.Bookmarks("O2ENGLEADMOB").Select
objWord.Selection.Text = Forms![Front Page]![txtTelNo]

objWord.ActiveDocument.Bookmarks("O2START").Select
objWord.Selection.Text = Forms![Front Page]![Text98]

objWord.ActiveDocument.Bookmarks("O2END").Select
objWord.Selection.Text = Forms![Front Page]![Text139]

'Word (or the document that you created with the template, will now open)
objWord.Visible = True

ActiveDocument.SendMail

Code:

Rx_ · May 21, 2013

I think you are asking to fill in the Outlook fields?
This will only run if Outlook is already running, it won't start outlook.

Code:

Private Sub CommandButtonLookOut_Click()  
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim strbody As String 
    Set OutApp = CreateObject("Outlook.Application") 
    OutApp.Session.Logon 
    Set OutMail = OutApp.CreateItem(0)  
    On Error Resume Next 
    With OutMail 
        .To = "YourStringVariable@here.com" 
        .CC = "" 
        .BCC = "" 
        .Subject = "The Filefor #" & Chr(32) & Range("H4") & " has been updated." 
        .Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName ' try the word doc here 
        .Send 'or use .Display
        .ReadReceiptRequested = True 
    End With 
    On Error Goto 0     
    Set OutMail = Nothing 
    Set OutApp = Nothing 
     
    Dim Msg, Style, Title 
    Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue." 
    Style = vbOKOnly + vbInformation 
    Title = "Open Issues List" 
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)      
End Sub

I don't have Outlook to test this, just Groupwise (so sad! to have Groupwise)

totalnovice2 · May 23, 2013

Thank you very much for replying.

The majority of this works well but I am stumped with the subject field.

If i need the subject to be just the start date (contained on the document as a bookmark) how do I do that.

So for example the word bookmark and the access field location are on the below.

Code:

objWord.ActiveDocument.Bookmarks("O2END").Select

objWord.Selection.Text = Forms![Front Page]![Text139]

Code:

Is it possible to have the subject as "Request - *** START DATE HERE*** - No update required"

Many thanks.

totalnovice2 · May 23, 2013

Hi.

I have done it.

i used the following code "

Code:

Dim acc_req As String

Code:

"

then "

Code:

acc_req = "O2 Access request" & " " & [Forms]![Front Page]![Text98].Value & " " & Forms![Front Page]![Site 2 Name].Value

Code:

"

then in the "outmail"

Code:

.Subject = acc_req

Code:

I still have another issue but think i have to post somewhere else on the site.

Rx_ · May 23, 2013

Looks like a solution. Glad to help.
You are taking the information from a text box property.
Eventually, you might want to grab the data from a recordset. If the Text98 is renamed later, the code must be updated too. Harvesting the data from a recordset adds to code maintenance efficiency.

Since you have On Error Resume Next - you also might want to take error handling to the next step. When using automation, it is preferred to isolate the error.

http://allenbrowne.com/ser-23a.html
This is an example of the Error Handling. The On Error ..... then the code at the bottom for handling an error. This will both help you identify the errors and manage them with out exposing messages to the user. We use to call it "exit gracefully".

Couldn't tell, hopefully the "Require Declaration" is turned on the code module.
This will add the words Option Explicit at the top of each module.
Then in the code window menu - Debug - Compile (project name)

Using Access VBA to Prefill Word Document Bookmarks then send on outlook. (1 Viewer)

totalnovice2

Registered User.

Rx_

Nothing In Moderation

totalnovice2

Registered User.

totalnovice2

Registered User.

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread