UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Manipulating Outlook Template Bookmarks (oft) Via Access Vba, Access 2016    
 
   
Sean-AUS-WA
post Dec 10 2019, 03:48 AM
Post#1



Posts: 58
Joined: 21-April 16
From: Perth, Australia


Good Morning Experts and Professionals!

I have conducted quite an extensive search online across multiple forums to no avail and have come crawling back to UA for your valuable assistance! Since I have started developing in access I have become quite familiar with some of the more common VBA codes associated with Outlook manipulation. This new tasks I am trying to undertake has an additional layer of complexity that I am unable to overcome with my current level of Access/Outlook knowledge crazy.gif
Scenario:
- I am trying to update/edit a few variables within a prepopulated Outlook .oft Template.
- I designed and saved an outlook email template (.oft) with picture letter head with a few paragraphs of prepopulated text.
- Within the template, I need to dynamically update a few variables pertaining to client details. i.e. Client number, Client Account number, Client name, etc.
- I have previously manipulated word document using form fields and bookmarks. Luckily while exploring Outlook template design I found that you can also insert a bookmark into the template.
- I created multiple bookmarks on the Email Template of where I want those variables to go.
- However I can't seem to find any resources online or otherwise with examples to manipulate bookmarks in Outlook Template from Access with VBA & along with some online articles I found, says that this is not possible?
- Other articles suggest editing a word document first then use the word document content as the email body.

Issues
- From what I can see, when my code reaches ' .body = " blah" ' it wipes out my picture along with everything else I have typed into the template beforehand.
- Instead of using .body, I am trying to update the bookmarks I have created with .bookmarks("ClientName").range.text = rst![ClientName].value but none of the Syntax I have tried seems to be working

Any advice/assistance will be gratefully appreciated! Many thanks in advance~b notworthy.gif


CODE
Private Sub Create_Email_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim appOutLook As Outlook.Application   '- Outlook Application
Dim MailOutLook As Outlook.MailItem '- Outlook Mail
Dim strAttachment1 As String   '- Original File Name
Dim strAttachment2 As String   '- Original File Name 2
Dim strPth As String           '- Original File Path
Dim Numb_Cycle As Integer

    strFldr = "C:\Users\sean\Desktop\Client\Attachment\"
    strAttachment1 = "Attach1.txt" '--file name"
    strAttachment2 = "Attach2.txt" '--file name"

    Set db = CurrentDb
    Set rs = Me.Recordset
    Set appOutLook = CreateObject("Outlook.Application")

  Numb_Cycle = 0

Do While Numb_Cycle <= 1
            Set MailOutLook = appOutLook.CreateItemFromTemplate("C:\Users\sean\Desktop\Client\NoticeTemplate.oft") '-- Intiate Outlook to create a new email message using Template
            With MailOutLook                            
                '-- Define and populate Email message
                .To = rs![emailaddress].value '-- Testing purposes
                .Subject = "This is a Notification Email"
                
                'This 2 line seems to clear all prepopulated text in the Outlook template.
                '.BodyFormat = olFormatRichText                
                '.HTMLBody = "Add body text here"

                 'I have tried the following syntax to no avail
                 .Bookmarks("ClientNumber").Range.Fields(1).Result.Text = rs![ClientNumb].value
                 .Bookmarks("ClientAccountNumber").Result.Text = rs![ClientAccountNumb].value
                 .Bookmarks("ClientName").Text = rs![ClientName].value

                 'Add attachments
                .Attachments.Add strFldr & strAttachment1 '-- Attachment #1 (File)
                .Attachments.Add strFldr & strAttachment2 '-- Attachment #2 (File)
                .Display

rs.MoveNext
Numb_Cycle = Numb_Cycle + 1

End With
Loop

Set objOutlook = Nothing
Set objMsg = Nothing
Set db = Nothing
Set rs = Nothing

End Sub






--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
June7
post Dec 10 2019, 04:28 AM
Post#2



Posts: 1,153
Joined: 25-January 16
From: The Great Land


I've never used .oft file and don't know about bookmarks in Outlook but I do have my Outlook set to include default signature (with image) in new email. I find the only way I can get the signature to be retained when automating Outlook with Access VBA is to concatenate HTMLBody and must follow Display.
CODE
    .Display
    .HTMLBody = "<html><p style='font-family:verdana;font-size:11pt'>Some message here</html>" & vbNewLine & .HTMLBody

This post has been edited by June7: Dec 10 2019, 04:29 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
June7
post Dec 10 2019, 07:23 AM
Post#3



Posts: 1,153
Joined: 25-January 16
From: The Great Land


I am finding Bookmarks is not a property or method of mail object. My research also indicates must use a Word object to manipulate the bookmarks. Review https://docs.microsoft.com/en-us/office/vba...em.GetInspector

I got this simple test to work.
CODE
Dim appOutLook As Outlook.Application   '- Outlook Application
Dim MailOutLook As Outlook.MailItem '- Outlook Mail
Dim wdDoc As Word.Document
Dim myInspector As Outlook.Inspector
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItemFromTemplate("C:\Users\June\Untitled.oft") '-- Intiate Outlook to create a new email message using Template
With MailOutLook
    '-- Define and populate Email message
    .To = ""
    .Subject = "This is a Notification Email"
    .Display
    'GetInspector property returns Inspector
    Set myInspector = .GetInspector
    'Obtain the Word.Document for the Inspector
    Set wdDoc = myInspector.WordEditor
    wdDoc.Bookmarks("Addressee").Range.Text = "June"
End With


This post has been edited by June7: Dec 10 2019, 07:28 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Sean-AUS-WA
post Dec 12 2019, 12:04 AM
Post#4



Posts: 58
Joined: 21-April 16
From: Perth, Australia


Good afternoon June!

I've found several sources that indicated the same thing. Manipulating the email directly does not seem to be possible and I must go through a word variant inspector of some sort but I was not able to get it to work.. Will trial your code shortly and come back to let you know how it went! Thanks a million!

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
Sean-AUS-WA
post Dec 12 2019, 01:17 AM
Post#5



Posts: 58
Joined: 21-April 16
From: Perth, Australia


Good afternoon June,

I have tried using your code but it continues to produce the 'Run-Time error '287', Application-defined or Object defined error' on the following line of the code that you have shared.

CODE
Set wdDoc = myInspector.WordEditor


I am unsure if this is to do with references that you have turned on where I haven't but I have included a picture of all the references I currently have enabled.
Would you be able to share a screenshot of your references as well? Thank you very much in advance!
This post has been edited by Sean-AUS-WA: Dec 12 2019, 01:17 AM
Attached File(s)
Attached File  References.JPG ( 37.7K )Number of downloads: 0
 

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
June7
post Dec 12 2019, 02:02 AM
Post#6



Posts: 1,153
Joined: 25-January 16
From: The Great Land


I don't have the SharePoint, Social, View libraries selected.

Otherwise you do have selected what is needed.

I am running Access 2010. Would not expect differences with later versions to be significant for this procedure.

This post has been edited by June7: Dec 12 2019, 02:14 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
June7
post Dec 12 2019, 02:39 PM
Post#7



Posts: 1,153
Joined: 25-January 16
From: The Great Land


An alternative to template and bookmarks is to build the entire email body within Access VBA.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 09:54 PM