Monthly Email

[Hayley Baxter]

I need to send out an email attachment on a monthly basis to a number of people. I would like this to happen without any user input if possible and go out on the same date every month. Can anyone help with this please.

Thank you
Hayley

 

[Nero]

Hayley,
Try this, I havn't tested it though but it might help.

Create a module and insert this code.
Public Function Mailer()

Dim olkapps As Outlook.Application
Dim olknamespaces As Outlook.NameSpace
Dim objmailitems As Outlook.MailItem

Set olkapps = New Outlook.Application
Set olknamespaces = GetNamespace("MAPI")
Set objmailitems = olkapps.CreateItem(olMailItem)
With objmailitems
.To = NameRef
.Subject = "Subject Title"
.Body = "Text In Here"
.Importance = olImportanceHigh
.Attachments = "C:\Your File"
.Send


End With

Set objmailitems = Nothing
Set olknamespaces = Nothing
Set olkapps = Nothing

End Function
_________________________________________
Then use this on the On_Load event for your form
Private Sub SendMail()
On Error GoTo Mailerr

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim MyDate As Date
Dim MailDate As Integer
Dim Msg

Set db = CurrentDb
Set rst = db.OpenRecordset("Your Table with the recipients names in")

MyDate = Now
MailDate = Left(MyDate,2)
If MailDate = "The date you want to send your email" Then

rst.MoveFirst

Do Until rst.EOF = True

NameRef = rst.Fields("Mail Name")
Mailer.Mailer
rst.MoveNext

Loop
Else Exit Sub
End If

Set rst = Nothing
Set db = Nothing

Mailend:
Exit Sub
Mailerr:
MsgBox Err.Description
Resume Mailend

End Sub

Kev.

 

[Hayley Baxter]

Thank You

Hi Nero

Thanks very much for your help with this I believe I am now close to achieving what I want.

Thanks again

Hay

 

[Nero]

Hayley,
Glad to help. Btw how do you get your avatar to animate??

Kev.

 

[Hayley Baxter]

Hi Kev

It was already an animated file which Fizzio found and Col passed onto me (better not take the credits here) I'm not sure how to make a standard image animated. Anyone else?

Hay

 

[erinkleiman]

Trouble with Outlook.Application Object

Hi Nero, Thank you for referring me to this thread but when I try out your code the compiler gives me an error in the line:

Dim olkapps As Outlook.Application

that says "user defined type not defined"

Do you know what the problem might be??

 

[Nero]

erinkleiman

In the VBA window go to Tools>References and tick the 'Microsoft Outlook 9.0 Library'.
That should do the trick.

Kev.

 

[erinkleiman]

When I go to Tools-> the 'References' link is not available (it's grey)??

 

[erinkleiman]

Oh nevermind....got it! Thanks a lot!

 

[Nero]

If you are in debug mode you will not be able to set references.
Make sure that you access the VB window whilst in design view of your form or stop the debugger.

Too Late!!

 

[erinkleiman]

Left function ??

Nero....
I want the database to send these emails automatically every month. It looks like your code tests for only one date, which means the email wouldn't be sent automatically every month. I am also a little confused about the line:

MailDate = Left(MyDate,2)

If MyDate is 7/12/02 for example, this makes MailDate = "7/"
and then you test to see if MailDate is equal to the date you want to send your mail?? This doesn't make sense to me? Thanks!

 

[Nero]

erinkleiman
Like I said to Hayley, I never tested the code I just wrote it on the fly. You could probably loose the left statement and just use a test to see if the current date is equal to your email send date.

Kev

 

[geraldcor]

It Almost Works

I did a search through the forum and found this code that should work beautifully for me assuming it works. I have copied it word for word (Save for my own database objects) and when I run it I get the error "The Operation Failed" Anyone have any ideas why this may be? It's a vague far out question but any help would be useful. Thank you. (I am only using the Mailer() function)

 

[Nero]

I did a search through the forum and found this code that should work beautifully for me assuming it works. I have copied it word for word (Save for my own database objects) and when I run it I get the error "The Operation Failed" Anyone have any ideas why this may be? It's a vague far out question but any help would be useful. Thank you. (I am only using the Mailer() function)

Have you tried stepping through the code to see where the problem is?

 

[geraldcor]

Yes I have. It seems ot fail on the .send operation. It tries to send it but when it gets to the next line it fails. I am assuming it is some sort of outlook problem but I don't know. Thank you for your help.
Greg

 

[Kodo]

The IDEAL solution would be to do this in a VBscript file and send your email out through an SMTP relay. If your box is running 2k/XP/2003, then you can set up IIS and use its' SMTP service to run emails through with out any requirement for Outlook and no fucking with it's security box.

THEN, you can use the task scheduler to run the vbscript at specified intervals. No need to even open the database at all. No need to use outlook. Completely hands free solution.

 

[Nero]

Yes I have. It seems ot fail on the .send operation. It tries to send it but when it gets to the next line it fails. I am assuming it is some sort of outlook problem but I don't know. Thank you for your help.
Greg

Try using .Display to see if it will at least display the email.
I've not tried Kodo's method so I can't comment.

 

[geraldcor]

It displays ok, (It does have some problems adding an attachment still) but it won't send. I don't know if this is what the code is really supposed to do but it is opening in Word as an email message. I would assume it would open outlook. Does it matter if I use Outlook or Outlook Express? I don't know if you know anything about this but I know Application.FollowHyperlink will open up the email account with address etc. but I haven't figured out how to attach files that way, (Probably not possible). Thank you again for helping me through this mess. It is so close to working.
Greg

 

[Kodo]

It displays ok, (It does have some problems adding an attachment still) but it won't send. I don't know if this is what the code is really supposed to do but it is opening in Word as an email message. I would assume it would open outlook. Does it matter if I use Outlook or Outlook Express? I don't know if you know anything about this but I know Application.FollowHyperlink will open up the email account with address etc. but I haven't figured out how to attach files that way, (Probably not possible). Thank you again for helping me through this mess. It is so close to working.
Greg

Greg, did you review my suggestion? I have a feeling it will work better for you.

 

[geraldcor]

Quite frankly Kodo, That is a little beyond my scope of knowledge. I am sure it would work if I just knew what it meant. Thank you for your suggestions however. if you think you can clarify that would be great.
Greg