Object Variable or With Variable Not Set

Hi Experts,

I am receiving the error in the title on the line:

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

Can someone tell me what I am missing or if I have this coded wrong?

Thanks,
Cody
Public Function EMailTest()
 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
'Dim MyNewBodyText As TextStream
Dim MyBodyText As String
 
Set fso = New FileSystemObject

Subjectline$ = "Email Test"
BodyFile$ = "C:\TestEmail.txt"

'Checks to see if body file is there

If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain??t Got No-Body!"
Exit Function
End If

'Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
'and read it into a variable.
MyBodyText = MyBody.ReadAll
'and close the file.
MyBody.Close

MyNewBodyText = MyBodyText
 
' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))

'Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
 
'Set up the database and query connections
Set db = CurrentDb()
 
Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

'now, this is the meat and potatoes.
'this is where we loop through our list of addresses,
'adding them to e-mails and sending them.

Do Until MailList.EOF

'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
 
'This addresses it
MyMail.To = MailList("Email")
 
'This gives it a subject
MyMail.Subject = Subjectline$
 
'This gives it the body
MyMail.Body = MyNewBodyText
 
'This sends it!
MyMail.Send

'And on to the next one...
MailList.MoveNext
Loop
 
'Cleanup after ourselves
Set MyMail = Nothing
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
End Function

Open in new window

Cody VanceSr. Analyst - ERPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You have it remmed out:

'Dim MyNewBodyText As TextStream      ' ***
Dim MyBodyText As String

But you have this later:

MyNewBodyText = MyBodyText
     ^^^^

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Hi MX,

Thanks for the reply.  I did uncomment that and it stops and gives me the same error at

MyNewBodyText = MyBodyText

Cody-
0
GRayLCommented:
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

should be:

MyNewBodyText = Replace(MyNewBodyText, "FirstName", MailList("FirstName"))

??  ie.  Why double brackes?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Cody VanceSr. Analyst - ERPAuthor Commented:
The double brackets it is the way it is input into the txt file for the body.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well you have

Dim MyNewBodyText As TextStream
Dim MyBodyText As String

and

MyBodyText = MyBody.ReadAll  ' Stream .. ?
'and close the file.
MyBody.Close

MyNewBodyText = MyBodyText   ' MyBodyText   is Text ??

mx
 
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This:

MyBodyText = MyBody.ReadAll
'and close the file.
    'MyBody.Close              '  Try moving the Close below the next line

MyNewBodyText = MyBodyText

MyBody.Close    '  *********************
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Moved it, still stops at  MyNewBodyText = MyBodyText

0
hnasrCommented:
Try this and comment:

Public Function EMailTest()
  
    Dim db As DAO.Database
    Dim MailList As DAO.Recordset
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    Dim Subjectline As String
    Dim BodyFile As String
    Dim fso 'As FileSystemObject
    Dim MyBody 'As TextStream
    Dim MyNewBodyText 'As TextStream
    Dim MyBodyText As String
  
    Set fso = CreateObject("Scripting.FileSystemObject")

 
    Subjectline$ = "Email Test"
    BodyFile$ = "C:\TestEmail.txt"
 
    'Checks to see if body file is there
 
    If fso.FileExists(BodyFile$) = False Then
        MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "I Ain??t Got No-Body!"
        Exit Function
    End If
 
    'Since we got a file, we can open it up.
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
    'and read it into a variable.
    MyBodyText = MyBody.ReadAll
    'and close the file.
    MyBody.Close
 
    MyNewBodyText = MyBodyText
  
    ' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
    MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
    MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))
 
    'Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application
  
    'Set up the database and query connections
    Set db = CurrentDb()
  
    Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")
 
    'now, this is the meat and potatoes.
    'this is where we loop through our list of addresses,
    'adding them to e-mails and sending them.
 
    Do Until MailList.EOF
 
        'This creates the e-mail
        Set MyMail = MyOutlook.CreateItem(olMailItem)
  
        'This addresses it
        MyMail.To = MailList("Email")
  
        'This gives it a subject
        MyMail.Subject = Subjectline$
  
        'This gives it the body
        MyMail.Body = MyNewBodyText
  
        'This sends it!
        MyMail.Send
 
        'And on to the next one...
        MailList.MoveNext
    Loop
  
    'Cleanup after ourselves
    Set MyMail = Nothing
    Set MyOutlook = Nothing
  
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
  
End Function

Open in new window

0
Cody VanceSr. Analyst - ERPAuthor Commented:
Could I change this to a DoCmd.SendObject?  Seems like it would be easier.  I pulled this code off of this website:

http://www.jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What is the exact error msg now ?

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
hnasr,

still stops at:

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Still the same error
0
Cody VanceSr. Analyst - ERPAuthor Commented:
mx,

It is the same error

Object Variable or With block Variable Not Set
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
How about dimming both to Stream

Dim MyNewBodyText As TextStream
Dim MyBodyText As TextStream
 
0
Cody VanceSr. Analyst - ERPAuthor Commented:
MX,

Same error with dimming both to TextStream
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What is the exact error now and what line does it occur on?

Also, have you tried Debug>>Compile ... to be sure there are no compile errors?

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Did Debug>Compile.  After dimming to textstream it stops at:

MyBodyText = MyBody.ReadAll

Same error
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, undim that as Stream I guess.

What is the Error message ?

mx
0
hnasrCommented:
codyvance1,
When I run the code from the immediate window, it displays the message "The body file isn't..."

After all these comments guess you need to attach a sample of the database.
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Hnasr,

If you create a file C:\TestEmail.txt with the following in the body:

[[Firstname]]: Test Email # of Guests: [[GuestCount]]

It will not give that to you.

MX,
It now stops at:

MyNewBodyText = MyBodyText

With same error.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"With same error."

What is the *error message* ?  Is it still "Object Variable or With Variable Not Set"

?
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Yes still that error MX
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Post the exact latest code you have now ... with changes.  Please post in this text box, not the 'Code' window.

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Public Function EMailTest()
 
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyNewBodyText As TextStream
Dim MyBodyText As String
 
Set fso = New FileSystemObject

Subjectline$ = "Email Test"
BodyFile$ = "C:\TestEmail.txt"

'Checks to see if body file is there

If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain??t Got No-Body!"
Exit Function
End If

'Since we got a file, we can open it up.
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
'and read it into a variable.
MyBodyText = MyBody.ReadAll
MyNewBodyText = MyBodyText
'and close the file.
MyBody.Close
 
' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))

'Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
 
'Set up the database and query connections
Set db = CurrentDb()
 
Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

'now, this is the meat and potatoes.
'this is where we loop through our list of addresses,
'adding them to e-mails and sending them.

Do Until MailList.EOF

'This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
 
'This addresses it
MyMail.To = MailList("Email")
 
'This gives it a subject
MyMail.Subject = Subjectline$
 
'This gives it the body
MyMail.Body = MyNewBodyText
 
'This sends it!
MyMail.Send

'And on to the next one...
MailList.MoveNext
Loop
 
'Cleanup after ourselves
Set MyMail = Nothing
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
End Function
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try changing

MyNewBodyText = MyBodyText

to

Set MyNewBodyText = MyBodyText

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Now i get type mismatch on that line.
0
hnasrCommented:
Add this line to declaring variables
Const ForReading = 1, ForWriting = 2, ForAppending = 3

What do you want to replace in this line?
    MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

Send or list a sample of the text file.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Problem is I'm not familiar with the Mail object model.

How about instead of

MyBodyText = MyBody.ReadAll
MyNewBodyText = MyBodyText

this:

Set MyNewBodyText = MyBody.ReadAll
'  MyNewBodyText = MyBodyText
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Ok now I get input past end of file.  Give me a second and I will send a sample DB.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Soooo ?  Why do I need a sample DB ?

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Here is the DB.
Sample.mdb
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Just figured it would be easier for you to look at then going back and forth.  Here is the txt file as well.
TestEmail.txt
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... so where and what is the error now?

mx
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Right now i get type mismatch at

Set MyNewBodyText = MyBody.ReadAll

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK .... first ... change

Dim MyNewBodyText As TextStream

To

Dim MyNewBodyText As Variant.

This will get us back to the original error line (same error):

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
          ' This has not been Set anywhere                                    ^^^^^^^

You Dimmed at the top      

Dim MailList As DAO.Recordset
 
but that's it.  

Somewhere   ... ok ... this below

Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

needs to be before      

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

like this I guess:

Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

?

mx
0
hnasrCommented:
Try this and comment:

Sample-2.mdb
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Yes hnasr that does work but does not replace the variables in the txt file with the values in the recordset.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And yet one more issue ... db  not set. This works:

Dim MyNewBodyText As Variant

' more ....

MyNewBodyText = MyBodyText
Set db = CurrentDb
Set MailList = db.OpenRecordset("qryTestEmail-MoreThan30")
' Now we can replace tokens to our heart's content without worrying about corrupting the "master" template
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"))
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Ok MX, you totally lost me... Can you reupload the DB for me to see please.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Standby ... still an issue ... Replace not replacing FirstName, only GuestCount ..

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here you go
Sample-MX01.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cody VanceSr. Analyst - ERPAuthor Commented:
Thank you MX, I really really appreciate it, this has been driving me up the wall!!
0
Cody VanceSr. Analyst - ERPAuthor Commented:
THANK YOU!!!!  You are a savior MX.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And ... make this change:

MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"), , , vbDatabaseCompare)
^^^^^^^^^^^^^^^^^^^^^
MyNewBodyText = Replace(MyNewBodyText, "[[GuestCount]]", MailList("GuestCount"), , , vbDatabaseCompare)

The last issue was Replace was not finding

[[FirstName]]

Because it's actually:

[[Firstname]]
         ^
So ... that option eliminates the Case issue.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ...  the    can be avoided by have the use Option Compare Database in the Declarations section of the module

Option Compare Database  ' ******** This was missing - causing Replace to be Case sensitive
Option Explicit
0
Cody VanceSr. Analyst - ERPAuthor Commented:
Ahhhh I did not know this...  I had issues before that changing from Compare to Explicit fixed so I just thought that might be the same issue.
0
Cody VanceSr. Analyst - ERPAuthor Commented:
MX one more thing, do you know where I could input to set the ActionTaken filed to true after it sends?

Thank You
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... I had to leave work ... missed your other Q.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.