Find text within email template (1 Viewer)

A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
Hi,
I have a db that creates an email from templates (the template changes depending on a selection). Each template contains key words that are replaced by data input by a user through the db using an InputBox. It generally works fine but I want to avoid using specific InputBoxes where not required (ie when there is no corresponding key word in the specific template). Here's relevant parts of my code:

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment

Please use Code Tags when posting VBA Code

To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...
www.access-programmers.co.uk

Code: 
Dim strFind As String
Dim strNew As String
Dim strInput As String
Dim strMsg As String
Dim sbjtCoy As String

Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\telephone call.oft")    'this is just one of the templates called- only one shown for simplicity

With MyMail
    With .Content.Find
    strFind = "company"
    .Text = "company"
    .Execute 'FindText:=strFind, Format:=False, Forward:=False
    If .Found = True Then
        strMsg = "Enter caller's company."
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        sbjtCoy = strInput    'used later in the module
        strInput = "from " & strInput
    End If
End With
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

Although the email is raised OK, it still doesn't work as I need. In the above extract, the InputBox is called up when it shouldn't. I'm guessing there's something wrong with how I'm calling the .Find propert. Any help would be very welcome.

PS, I'm not a 'real' programmer, so please be gentle:)
Andy
 
Last edited by a moderator:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:11
Joined
May 7, 2009
Messages
19,237
maybe change the IF .Found = True Then to IF .Found = False Then
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 16:11
Joined
Sep 21, 2011
Messages
14,270
I cannot even find .Content.Find with intellisense?

Does it even compile?
 
Last edited:
A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
Hi
Thanks for the suggestions.
I tried changing the statement to FALSE, but it still doesn't run correct. I guess that could indicate that the problem is with the .find property.
Also, it does compile.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 16:11
Joined
Sep 21, 2011
Messages
14,270
Well I can see you have to With statements and only one End With?
I get the following error on the .Content.Find line?
1593521261637.png
 
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
Gasman said:
Well I can see you have to With statements and only one End With?
Click to expand...
Likely it is just not shown in the snippet. Also, the error suggests a references problem.
@Andy Mc - please enclose more than a few lines of code within code tags (</> on toolbar)
 
A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
Hi. Sorry had to be out for a few hours. I've pasted the whole code below (this might clarify a few things).
I got the .Content.Find procedure from a MS website (which I attempted to modify for my needs).

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Code: 
Option Compare Database
Option Explicit

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim TmplFile As DAO.Recordset
Dim myOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim fso As FileSystemObject
Dim sbjtName As String
Dim sbjtTel As String
Dim strFind As String
Dim strNew As String
Dim Start As Variant
Dim strInput As String
Dim strMsg As String
Dim sbjtCoy As String

Set fso = New FileSystemObject

On Error Resume Next
       
Set myOutlook = GetObject(, "Outlook.Application")

If myOutlook Is Nothing Then
   
    Run CheckOutlook()
   
End If

'clear recordset for EmailClient & populate with current client details
DoCmd.OpenQuery "ClearEmailClient"
DoCmd.OpenQuery "ClientEmail"


Set db = CurrentDb()
Set MailList = db.OpenRecordset("EmailClient")
Set TmplFile = db.OpenRecordset("files")
Set myOutlook = New Outlook.Application


'check for a personal template
If Not (Dir(TmplFile("template") & "\" & MailList("Abbr") & " " & MailList("First") & ".oft")) = "" Then
   
    'create email using personal template
    Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\" & MailList("Abbr") & " " & MailList("First") & ".oft")

'check for a company template
ElseIf Not (Dir(TmplFile("template") & "\" & MailList("Abbr") & ".oft")) = "" Then
   
    'create email using company template
    Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\" & MailList("Abbr") & ".oft")

'use general template
Else
   
    'create email using general template
     Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\telephone call.oft")
       
        'check template exists
        If Dir(TmplFile("template") & "\telephone call.oft") = "" Then
            MsgBox "The template does not exist"
            Exit Function
        End If

        'check if there's no email address for this person
        If IsNull(MailList("Email Address")) Then
            MsgBox "There is no email address for this client on the database. Check for an address in Outlook."
            DoCmd.OpenQuery "ResetSelect"
            DoCmd.OpenQuery "ClearEmailClient"
            Exit Function
        Else
            MyMail.To = MailList("Email Address")
        End If
       
End If

'get new info to populate email template
With MyMail
    
    With .Content.Find
        strFind = "company"
        .Text = "company"
        .Execute  'FindText:=strFind, Format:=False, Forward:=False
        If .Found = False Then
            strMsg = "Enter caller's company."
            strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
            sbjtCoy = strInput
            strInput = "from " & strInput
        End If
    End With
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)
   
    strFind = "greeting"
    If Hour(Now()) < 12 Then
        strNew = "Good morning"
    Else
        strNew = "Good afternoon"
    End If
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strNew)
   
    strFind = "cname"
    strNew = MailList("First")
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strNew)
     
    With .Find
    .ClearFormatting
    strFind = "person"
    .Execute FindText:=strFind, Format:=False, Forward:=False
    If .Found = True Then
        strMsg = "Enter caller's name."
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        sbjtName = strInput
    End If
    End With
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

    With .Content.Find
    .Text = "TelNumber"
    .Forward = True
    .Execute
    If .Found = True Then
        strMsg = "Enter caller's phone number(s)"
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        strFind = "TelNumber"
        sbjtTel = strInput
    End If
    End With
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)
   
    With .Content.Find
    .Text = "comment"
    .Forward = True
    .Execute
    If .Found = True Then
        strMsg = "Enter comments"
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        strFind = "comment"
    End If
    End With
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

    strFind = "person, company"
    If sbjtCoy = "" Then
        .Subject = Replace(MyMail.Subject, strFind, sbjtName)
    Else
        .Subject = Replace(MyMail.Subject, strFind, sbjtName & " from " & sbjtCoy)
    End If
       
    strFind = "signature"
    .HTMLBody = Replace(MyMail.HTMLBody, strFind, TmplFile("user"))
  
End With

'display email
    MyMail.Display
    'Set focus to Outlook
    AppActivate "Inbox - Microsoft Outlook"

'tidy up
DoCmd.OpenQuery "ResetSelect"
DoCmd.OpenQuery "ClearEmailClient"
DoCmd.OpenQuery "ClearSelect"
Set MyMail = Nothing
Set myOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function
 
Last edited by a moderator:
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
I guess you didn't see or didn't understand my request to use code tags - or worse. It's the only way to properly indent and equally space code, making it easier to decipher.
 
A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please feel free to Remove this Comment

Please use Code Tags when posting VBA Code

To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...
www.access-programmers.co.uk

Code: 
Option Compare Database
Option Explicit

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim TmplFile As DAO.Recordset
Dim myOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim fso As FileSystemObject
Dim sbjtName As String
Dim sbjtTel As String
Dim strFind As String
Dim strNew As String
Dim Start As Variant
Dim strInput As String
Dim strMsg As String
Dim sbjtCoy As String

Set fso = New FileSystemObject

On Error Resume Next

Set myOutlook = GetObject(, "Outlook.Application")

If myOutlook Is Nothing Then

Run CheckOutlook()

End If

'clear recordset for EmailClient & populate with current client details
DoCmd.OpenQuery "ClearEmailClient"
DoCmd.OpenQuery "ClientEmail"

Set db = CurrentDb()
Set MailList = db.OpenRecordset("EmailClient")
Set TmplFile = db.OpenRecordset("files")
Set myOutlook = New Outlook.Application

'check for a personal template
If Not (Dir(TmplFile("template") & "\" & MailList("Abbr") & " " & MailList("First") & ".oft")) = "" Then

    'create email using personal template
    Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\" & MailList("Abbr") & " " & MailList("First") & ".oft")

    'check for a company template
   
    ElseIf Not (Dir(TmplFile("template") & "\" & MailList("Abbr") & ".oft")) = "" Then

        'create email using company template
        Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\" & MailList("Abbr") & ".oft")

    'use general template
Else

    'create email using general template
    Set MyMail = myOutlook.CreateItemFromTemplate(TmplFile("template") & "\telephone call.oft")

    'check template exists
    If Dir(TmplFile("template") & "\telephone call.oft") = "" Then
        MsgBox "The template does not exist"
        Exit Function
    End If

    'check if there's no email address for this person
    If IsNull(MailList("Email Address")) Then
        MsgBox "There is no email address for this client on the database. Check for an address in Outlook."
        DoCmd.OpenQuery "ResetSelect"
        DoCmd.OpenQuery "ClearEmailClient"
        Exit Function
    Else
    MyMail.To = MailList("Email Address")
    End If

End If

'get new info to populate email template
With MyMail

With .Content.Find
    strFind = "company"
    .Text = "company"
    .Execute 'FindText:=strFind, Format:=False, Forward:=False
    If .Found = False Then
        strMsg = "Enter caller's company."
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        sbjtCoy = strInput
        strInput = "from " & strInput
    End If
End With
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

strFind = "greeting"
If Hour(Now()) < 12 Then
    strNew = "Good morning"
Else
    strNew = "Good afternoon"
End If
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strNew)

strFind = "cname"
strNew = MailList("First")
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strNew)

With .Content.Find
    .ClearFormatting
    strFind = "person"
    .Execute FindText:=strFind, Format:=False, Forward:=False
    If .Found = True Then
        strMsg = "Enter caller's name."
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        sbjtName = strInput
    End If
End With
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

With .Content.Find
    .Text = "TelNumber"
    .Forward = True
    .Execute
    If .Found = True Then
        strMsg = "Enter caller's phone number(s)"
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        strFind = "TelNumber"
        sbjtTel = strInput
    End If
End With
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

With .Content.Find
    .Text = "comment"
    .Forward = True
    .Execute
    If .Found = True Then
        strMsg = "Enter comments"
        strInput = InputBox(Prompt:=strMsg, Title:="Leave blank if not known/relevant")
        strFind = "comment"
    End If
End With
.HTMLBody = Replace(MyMail.HTMLBody, strFind, strInput)

strFind = "person, company"
    If sbjtCoy = "" Then
    .Subject = Replace(MyMail.Subject, strFind, sbjtName)
Else
    .Subject = Replace(MyMail.Subject, strFind, sbjtName & " from " & sbjtCoy)
End If

strFind = "signature"
.HTMLBody = Replace(MyMail.HTMLBody, strFind, TmplFile("user"))

End With

'display email
MyMail.Display
'Set focus to Outlook
AppActivate "Inbox - Microsoft Outlook"

'tidy up
DoCmd.OpenQuery "ResetSelect"
DoCmd.OpenQuery "ClearEmailClient"
DoCmd.OpenQuery "ClearSelect"
Set MyMail = Nothing
Set myOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function
 
Last edited by a moderator:
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
You could try stepping through the code to ensure that Access has enough time to return a value from another app before moving on. However, I think your issue is that .Find doesn't seem to be a method of the Outlook.MailItem.Content property (or whatever that is). Perhaps post a link to where you got that from if you want an opinion on that. I searched the Outlook object model and while I saw Find, it wasn't a method for what you're trying to apply it to.

Another issue that you've introduced is your use of Resume Next. That's OK when checking for the existence of certain objects or properties, then taking appropriate action in what comes next, but you should not leave it at that. If this use of the Find method is raising an error, you are telling Access to just carry on without notice. After proper use of Resume Next, enable error handling with a different directive to an error handler e.g. - On Error GoTo errHandler (obviously you need to write this handler). Additionally, I would never write a procedure such as this without an error handler as too much can go wrong, especially if you've re-enabled error handling after using Resume Next.

Your indentation didn't save because you didn't use code tags? Feel free to go back and edit your post to remove what UG posted - as he invites.
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 08:11
Joined
Mar 14, 2017
Messages
8,777
I'm with Gasman on this one. According to this link, if I'm looking at the right article for this situation, I can't find Content as a property nor method of the MailItem object.

And, since you have the very much NOT-recommended (cannot be emphasized enough) On Error Resume Next in your code, you aren't even realizing that Content doesn't exist, because On Error Resume Next is forcing VBA to fail to raise that error!

It seems like your problem is not with your IF statement, True/False, etc. It's referring to a property or method that doesn't exist.
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 08:11
Joined
Mar 14, 2017
Messages
8,777
I guess I could add one more helpful thing.

You can test the MailItem.Body, and use Instr(). Regardless of the other stuff, I know that should work, as I've used it before.

Code: 
If Instr(1,MyMail.Body,"Text to Search For")>0 then
    'then it was found, code continues
Else
    'it was not found
End If
 
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
pisorsisaac@gmail.co said:
And, since you have the very much NOT-recommended (cannot be emphasized enough) On Error Resume Next in your code
Click to expand...
Maybe we'll just agree to disagree, but this is acceptable under certain circumstances, such as when setting a db property that may not exist. If it doesn't exist, you can't check it's value, so you attempt to set it. If you simply try to create it without testing for it's existence, you can still raise an error because it already exists. Resume Next allows you to handle the result of the test or at least allows you to direct flow. Here's just one snippet of many proper examples you can find using Resume Next. Here's one that applies to db properties that I'm referring to:

Code: 
   ' Error 3270 means that the property was not found.
   If DBEngine.Errors(0).Number = 3270 Then
      ' Create property, set its value, and append it to the 
      ' Properties collection.
      Set prpNew = dbsTemp.CreateProperty(strName, _
         dbBoolean, booTemp)
      dbsTemp.Properties.Append prpNew
      Resume Next
Resume Next will resume after the property setting line that raises an error because the property didn't exist. Now that you've branched off and created the property, you pick up from the error and set the property value.

This is from Allen Browne code, where the appropriate action seems to be ''present a message, do something then exit'.
Code: 
On Error Resume Next
    Set DB = ws.OpenDatabase(ext)
    If Err Then
        MsgBox "Can't open """ & ext & """: " & Error, 48
        checkcompat = False
        Exit Function
    End If
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 08:11
Joined
Mar 14, 2017
Messages
8,777
Of course, that is the valid exception. We agree. I didn't state "Never", but if it seemed implied, I should have added a clarification/exception-thanks.
And I certainly didn't state that Resume Next is inappropriate.

I'm talking about in normal code like the OP had it. I'm talking about just throwing it in there under normal circumstances. And my advice certainly applied in this case. And turned out to be the root cause of the confusion as well.
PS Happy Canada Day
 
Last edited:
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
OK. 'Can't stress enough that it's not recommended' kind of set you up I guess.
P.S. - Thanks for the CD wishes. You are not Canadian though, I think? So that's especially kind if you are not. I would not expect most from south of the border neither know about the significance of today for us, or would care enough to say that. 🙏
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 08:11
Joined
Mar 14, 2017
Messages
8,777
Micron said:
OK. 'Can't stress enough that it's not recommended' kind of set you up I guess.
Click to expand...
I suppose. I just see it so much when people are learning VBA and 99% of those type of scenarios it's there for no reason. But it has its place. Even in half of the times that it's used, I'd probably recommend using an error handler and trapping a specific error, rather than using it as "there's something I want to test, and I'm 99% sure that if any error is raised, it will be the error I think it is".....which is the way it's often used, even in the scenarios you mentioned. But now I'm just being insufferably picky.

I think I read about Canada Day on the news this morning somewhere. Yes, absolutely. I love what I imagine Canada might be like, will get up there some day.
 
A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
I got the idea for searching for text from https://docs.microsoft.com/en-us/of...word/finding-and-replacing-text-or-formatting. I know that this was a Word article, but I had hoped it could be modified for Access.

Sorry about the On Error thing; it was in some code I picked up (can't recall where) when I first added this to my db. I must have failed to copy the other lines.

I like using Access, and I'm the first to admit that I'm no programmer, but I'm beginning to realise that I've exceeded my very limited knowledge :(! Perhaps I should just concentrate on my day job :)

Belated Happy Canada Day. It's been many years since I was last over there (always had a great time with the people I was working with).
 
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
Andy Mc said:
Perhaps I should just concentrate on my day job
Click to expand...
Unless one is born with all Access knowledge, how do you think any of us got to know what we know? Stick with it!
There is some commonality between the various vba models but you cannot just borrow anything from one and expect it to work in another. Having said that, IF you have incorporated the required references, you can manipulate other application objects and make use of its methods, properties and events from a different app. Additionally, you will often get intellisense prompts when typing as long as the required references are set and you're using early binding. Thus if you type MyMail.Content. and Find isn't offered in a list of options, you might not have the reference established. IIRC, this can also happen with certain code constructs (I'm thinking With blocks can be one of them).

Thanks for the wishes. It was a quiet, peaceful day on the deck!
 
A

Andy Mc

New member
Local time
Today, 16:11
Joined
May 6, 2012
Messages
16
Thanks for all that, and for the encouragment. I will stick with it, but perhaps I'll lower my ambitions for now. It just seemed a simple thing to try to inspect an email to find if a particular text exists.

Peaceful is the new norm these days - well , at least for sensible people. Our pubs are reopening this weekend and goodness knows what will happen as a result :(
 
M

Micron

AWF VIP
Local time
Today, 11:11
Joined
Oct 20, 2018
Messages
3,478
Don't forget that I mentioned that this might work if you have the required references and are not using late binding.
 
You must log in or register to reply here.

Similar threads

S
Email Template VBA Link
Replies
1
Views
649
Gasman
Gasman
M
Multiple attachments and a report in the same email
Replies
15
Views
938
Marshall Brooks
M
M
VBA output report to PDF and email
Replies
18
Views
759
Pat Hartman
P
R
get the varilbe " strinput" into a query
2
Replies
20
Views
1,188
Pat Hartman
P
S
Access VBA to Find Row and Col for found text in MS Word
Replies
3
Views
565
sxschech
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom