Question Can Access Email?

[hardhitter06]

Hi All,

Using Access 2003...

What I would like to do is at the end of every month, have an email sent to one of my co workers informing them on the Amount of records that were added for that month.

I only have one table and one form for this entire database because the users will never need to do a search.

Is this or something like this possible in Access?

 

[pbaldy]

Sure; the simplest method is SendObject in VBA.

 

[hardhitter06]

Could you show me what some coding would look like to do this?

 

[pbaldy]

Look in VBA Help and search here on it, which should turn up tons of examples. If you're still stuck, post your effort and we'll fix it.

 

[irish634]

If you want this done automatically, look up "CDO Email".

 

[hardhitter06]

I found this:

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

I figured I would just make a Form/Report that displays all records within the current month and create a counter or whatever.

But, how would I use this code to literally email this person at the end of each month.

In otherwords, what would I put the code behind and also, how exactly would I do the date function saying "Email to....Joe Smith"? When its lets say Dec 31st

 

[pbaldy]

Keep in mind that you don't have to actually send an object, you can just send an email with no attachment. If all you want is a count, you can use a DCount or recordset to count the records and just include that in the body of the email.

If you want the process to happen automatically, I'd probably create a little mdb that performed this function when it started, then it closed itself. Then I'd call that mdb from Scheduled Tasks on the appropriate schedule.

 

[hardhitter06]

Alright, thank you, that was very helpful . I'm leavin from work soon, but I will try Monday and will let you know where I'm at. Thanks again for the guidance.

 

[MSAccessRookie]

I found this:

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

I figured I would just make a Form/Report that displays all records within the current month and create a counter or whatever.

But, how would I use this code to literally email this person at the end of each month.

In otherwords, what would I put the code behind and also, how exactly would I do the date function saying "Email to....Joe Smith"? When its lets say Dec 31st

Consider using it in a manner similar to this

    Dim stDocName As String
    Dim stDocSubj As String
    Dim stDocEmail As String
 
    stDocName = "Email"
    stDocSubj = "Email Being Sent to " & Me.ContactName ' email subject line goes here
    stDocEmail = [EMAIL="ContactName@Server.net"]ContactName@Server.net[/EMAIL] ' email address goes here
 
    DoCmd.SendObject acSendNoObject, stDocName, acFormatXLS, stDocEmail, , , stDocSubj, , True

 

[hardhitter06]

Very nice MsAccess!

 

[hardhitter06]

MsAccess, where do I put that code? Or do i just open up VB and paste it in?

 

[MSAccessRookie]

Very nice MsAccess!

This format opens an email and displays the contents of a file called email. I believe that if you make stDocName a Text Message, that mesage will appear there instead. You may also need to change the True on the End to False, if you want to sent it without approving it.

 

[MSAccessRookie]

MsAccess, where do I put that code? Or do i just open up VB and paste it in?

Only you can answer that. You would want to put it behind the event where you find the need for it. For instance, this example was a part of the DBL Click Event for the email field on one of our user entry forms. I would imagine that you can determine a point where you have the need to send an email, and place it there

 

[hardhitter06]

Oh I see...so I would have to add more code with what we have already to create a count from my table?

Well to respond to your latest response, this is like a reminder to this one specific user that he has to publish the new records to a website. I might not even need the record count, but I would like Access to email this person at the end of every month stating "Please upload new records to the website" or whatever. I threw in the record count to work as a double check, to make sure he actually copies and pastes all the records..

 

[MSAccessRookie]

Oh I see...so I would have to add more code with what we have already to create a count from my table?

something like that. Without seeing hte database and knowing the data flow, I cannot be more precise. Think of it this way. I am sure that your program knows when it has completed whatever you want to report as completed, and that would be when you would want to send an email (if it is automatic). Alternatively, you could always add a Command Button and place the email action in the On Click Event

 

[hardhitter06]

Well I guess I'm worried about what if no one uses the database the last day of the month, the database will need to be opened for the email to send right?

And with what you said, I don't want to do a Command Button because that would mean someone has to go in there and click it at the end of the month, and also, this specific user will only be visiting the database to copy and print the table once they receive the email, well at the end of every month but the email is a reminder to do so.

I was kind of confused when you said, "i am sure your program knows when it has completed whatever you want to report as completed"...

 

[MSAccessRookie]

Well I guess I'm worried about what if no one uses the database the last day of the month, the database will need to be opened for the email to send right?

And with what you said, I don't want to do a Command Button because that would mean someone has to go in there and click it at the end of the month, and also, this specific user will only be visiting the database to copy and print the table once they receive the email, well at the end of every month but the email is a reminder to do so.

I was kind of confused when you said, "i am sure your program knows when it has completed whatever you want to report as completed"...

Whenever you run a program, a series of activities occur. At some point during these activities (probably somewhere near the end), the computer will need to report the results of its activities via email. During that step of the programs activities, and before the program exits, you would add the code to email the results.