Name and a body of file

[bunji]

I have re-written some code that will send an email without the outlook security notice, which works fine, However i would like to address each email with the persons name.

With OlMailItem
.To = MailList("EMailAddress")
.Subject = "Harrodian Flyer NewsLetter"
.Body = "Dear " And MailList("Name") And MyBodyText
.Attachments.Add "c:\harrodianNewsLetter\Harrodian Flyer.pdf"

End With

However on .body = i get an error message of Type Mismatch.

How can i put in what i want above.

 

[Jibbadiah]

You may need something like this...

Dim strrecipent as string
Dim strBody as string
Dim db as Database
Dim rst as Recordset

Set db = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT Name, MyBodyText FROM MailList;")")

rst.MoveFirst
Do Until rst.EOF

strRecipent = rst!Name
strBody = rst!myBodyText

With OlMailItem
.To = MailList("EMailAddress")
.Subject = "Harrodian Flyer NewsLetter"
.Body = "Dear " & strRecipent & MyBodyText
.Attachments.Add "c:\harrodianNewsLetter\Harrodian Flyer.pdf"

End With


rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

 

[bunji]

Sorry baout the late reply, but here is my code and i still get the same error doing it the way you suggested.

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset

Dim OlApp As Outlook.Application
Dim olNamespace As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlMailItem As Outlook.MailItem
Dim objSafeMail As Redemption.SafeMailItem


Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim RecipName As String


Set fso = New FileSystemObject
Set db = CurrentDb()

'Get the data
Set MailList = db.OpenRecordset("QryCustomerReceiveEmailNewsletter")
Do Until MailList.EOF

RecipName = MailList("Name")

Set OlApp = CreateObject("Outlook.application") 'Create an instance of Redemption.SafeMailItem
Set olNamespace = OlApp.GetNamespace("MAPI")
Set OlFolder = olNamespace.GetDefaultFolder(olFolderInbox)
Set OlMailItem = OlFolder.Items.Add("IPM.note")

BodyFile$ = "c:\harrodianNewsLetter\EmailBody.txt"

' Check to make sure the file exists...
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 = fspenTextFile(BodyFile, ForReading, False, TristateUseDefault)

' and read it into a variable.
MyBodyText = MyBody.ReadAll

' and close the file.
MyBody.Close

'Give the email item its contents
With OlMailItem
.To = MailList("EMailAddress")
.Subject = "Harrodian Flyer NewsLetter"
.Body = "Dear " And RecipName And MyBodyText
.Attachments.Add "c:\harrodianNewsLetter\Harrodian Flyer.pdf"

End With

Set objSafeMail = New Redemption.SafeMailItem
objSafeMail.Item = OlMailItem
objSafeMail.Send

'instead of automaticially sending it
'Uncomment the next line to see the email
'And comment the " objSafeMail.Send" line above this.
'objSafeMail.Display

'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves
Set objSafeMail = Nothing
Set OlMailItem = Nothing
Set OlFolder = Nothing
Set olNamespace = Nothing
Set OlApp = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[Jibbadiah]

Can you post a sample database in Access 97 and I'll take a look.
At the moment I can see a few errors and would need to step through your code to correct them.
Recreate a database with the basics and don't include any personal information.

James

 

[bunji]

Ok i have stripped it all the un-neccessary stuff and works fine as xp except the bit i am trying to acheive. I dont have 97 so cant test it but hope the conversion went through fine.

Thanks

 

[Jibbadiah]

You haven't attached a db?

 

[Jibbadiah]

Oops... ignore... didn't show up the first time for some reason.
Page must have still been loading... will take a look.

J

 

[Jibbadiah]

Try this

Bunji,

Your current code looks unecessarily complicated.
Copy this exactly as it stands into a new module and step through it.
At this stage it is only the basics to show how it should work.
I have tested it against your attached db and it works ok.
Add your additional lines of code as required.

Public Function SendEMail()

Dim db As Database
Dim MailList As Recordset
Dim OlApp As Outlook.Application
Dim olNamespace As Outlook.NameSpace
Dim OlFolder As Outlook.MAPIFolder
Dim OlMailItem As Outlook.MailItem

    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("QryCustomerReceiveEmailNewsletter")
    
    Do Until MailList.EOF
    
        Set OlApp = CreateObject("Outlook.application")
        Set olNamespace = OlApp.GetNamespace("MAPI")
        Set OlFolder = olNamespace.GetDefaultFolder(olFolderInbox)
        Set OlMailItem = OlFolder.Items.Add("IPM.note")

    With OlMailItem
        .To = MailList("EMailAddress")
        .Subject = "Harrodian Flyer NewsLetter"
        .Body = "Dear " & MailList("Name") & vbCrLf & vbCrLf & _
        "Please find attached our newsletter for this week." & vbCrLf & vbCrLf & _
        "We hope that you enjoy the read." & vbCrLf & vbCrLf & _
        "Regards," & vbCrLf & vbCrLf & _
        "Bunji" & vbCrLf & vbCrLf
        .Attachments.Add "c:\harrodianNewsLetter\EmailBody.txt"
        .Send

    End With
    
    
    MailList.MoveNext

Loop
    
End Function

Take the time to understand what any code does before you use it!!

James

 

[bunji]

OK, i see how that works, however i didnt want to hardcode the message has a user may wish to change what the message says, so they can change it by editing the text file. Thats why i got the code to read in the file, that the users can change. The problem occurs when trying to combine the recipients name and the contents of the file.

 

[bunji]

I have tried your code. It produces the Outlook Security message that another program is trying to use outlook, thats why the code i have may appear complicated but it avoids this issue. Outlook 97 doesnt have this security feature.

 

[izyrider]

?? what's this
.Body = "Dear " And RecipName And MyBodyText

"Dear " and RecipName and MyBodyText = False (boolean, and certainly not string)

and (logical) <> & (concatenation)



assuming RecipName and MyBodyText are string, try:
.body = "Dear " & RecipName & MyBodyText



izy

 

[Jibbadiah]

Thanks Izy.

With OlMailItem
.To = MailList("EMailAddress")
.Subject = "Harrodian Flyer NewsLetter"
.Body = "Dear " & strRecipent & MyBodyText
.Attachments.Add "c:\harrodianNewsLetter\Harrodian Flyer.pdf"

That is what I gave Bunji initially.

Bunji - That is why I kept it simple and asked you to step through it and understand it. If someone gives you code, you should normally take it literally. Don't change anything until you have tried what they have given you.

The

& vbCrLf

adds an extra line to your text
The

& MailList("Name")

is extracting the value for recipient for each record in the loop.

When you open a recordset...

Set MailList = db.OpenRecordset("QryCustomerReceiveEmailNewsletter")

... you have the ability to use any of the values in that recordset.
If your query referenced the bodytext you could use that in the same way that you are using recipientname.

HTH

James

 

[bunji]

Ok that Worked, thanks you two for your help with this and making it a little easier to understand.