[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Object Variable or With Variable Not Set

Posted on 2010-04-06
48
Medium Priority
?
306 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Cody Vance
  • 23
  • 20
  • 4
  • +1
48 Comments
 
LVL 75
ID: 29953162
You have it remmed out:

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

But you have this later:

MyNewBodyText = MyBodyText
     ^^^^

mx
0
 

Author Comment

by:Cody Vance
ID: 29953353
Hi MX,

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

MyNewBodyText = MyBodyText

Cody-
0
 
LVL 44

Expert Comment

by:GRayL
ID: 29953732
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))

should be:

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

??  ie.  Why double brackes?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Cody Vance
ID: 29953861
The double brackets it is the way it is input into the txt file for the body.
0
 
LVL 75
ID: 29954074
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
 
LVL 75
ID: 29954241
This:

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

MyNewBodyText = MyBodyText

MyBody.Close    '  *********************
0
 

Author Comment

by:Cody Vance
ID: 29954428
Moved it, still stops at  MyNewBodyText = MyBodyText

0
 
LVL 31

Expert Comment

by:hnasr
ID: 29954498
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
 

Author Comment

by:Cody Vance
ID: 29954503
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
 
LVL 75
ID: 29954523
What is the exact error msg now ?

mx
0
 

Author Comment

by:Cody Vance
ID: 29954588
hnasr,

still stops at:

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

Author Comment

by:Cody Vance
ID: 29954623
Still the same error
0
 

Author Comment

by:Cody Vance
ID: 29954728
mx,

It is the same error

Object Variable or With block Variable Not Set
0
 
LVL 75
ID: 29954763
How about dimming both to Stream

Dim MyNewBodyText As TextStream
Dim MyBodyText As TextStream
 
0
 

Author Comment

by:Cody Vance
ID: 29954927
MX,

Same error with dimming both to TextStream
0
 
LVL 75
ID: 29955080
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
 

Author Comment

by:Cody Vance
ID: 29955169
Did Debug>Compile.  After dimming to textstream it stops at:

MyBodyText = MyBody.ReadAll

Same error
0
 
LVL 75
ID: 29955379
Well, undim that as Stream I guess.

What is the Error message ?

mx
0
 
LVL 31

Expert Comment

by:hnasr
ID: 29955410
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
 

Author Comment

by:Cody Vance
ID: 29955610
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
 
LVL 75
ID: 29956106
"With same error."

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

?
0
 

Author Comment

by:Cody Vance
ID: 29956247
Yes still that error MX
0
 
LVL 75
ID: 29956532
Post the exact latest code you have now ... with changes.  Please post in this text box, not the 'Code' window.

mx
0
 

Author Comment

by:Cody Vance
ID: 29956817
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
 
LVL 75
ID: 29957085
Try changing

MyNewBodyText = MyBodyText

to

Set MyNewBodyText = MyBodyText

mx
0
 

Author Comment

by:Cody Vance
ID: 29957182
Now i get type mismatch on that line.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 29957503
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
 
LVL 75
ID: 29957552
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
 

Author Comment

by:Cody Vance
ID: 29957653
Ok now I get input past end of file.  Give me a second and I will send a sample DB.
0
 
LVL 75
ID: 29957894
Soooo ?  Why do I need a sample DB ?

mx
0
 

Author Comment

by:Cody Vance
ID: 29958287
Here is the DB.
Sample.mdb
0
 

Author Comment

by:Cody Vance
ID: 29958393
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
 
LVL 75
ID: 29958492
ok ... so where and what is the error now?

mx
0
 

Author Comment

by:Cody Vance
ID: 29958601
Right now i get type mismatch at

Set MyNewBodyText = MyBody.ReadAll

0
 
LVL 75
ID: 29959738
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
 
LVL 31

Expert Comment

by:hnasr
ID: 29959936
Try this and comment:

Sample-2.mdb
0
 

Author Comment

by:Cody Vance
ID: 29960228
Yes hnasr that does work but does not replace the variables in the txt file with the values in the recordset.
0
 
LVL 75
ID: 29960306
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
 

Author Comment

by:Cody Vance
ID: 29961142
Ok MX, you totally lost me... Can you reupload the DB for me to see please.
0
 
LVL 75
ID: 29961260
Standby ... still an issue ... Replace not replacing FirstName, only GuestCount ..

mx
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 29961406
Here you go
Sample-MX01.zip
0
 

Author Comment

by:Cody Vance
ID: 29961416
Thank you MX, I really really appreciate it, this has been driving me up the wall!!
0
 

Author Closing Comment

by:Cody Vance
ID: 31711569
THANK YOU!!!!  You are a savior MX.
0
 
LVL 75
ID: 29961727
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
 
LVL 75
ID: 29961893
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
 

Author Comment

by:Cody Vance
ID: 29961949
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
 

Author Comment

by:Cody Vance
ID: 29965688
MX one more thing, do you know where I could input to set the ActionTaken filed to true after it sends?

Thank You
0
 
LVL 75
ID: 29976533
Sorry ... I had to leave work ... missed your other Q.

mx
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question