• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Sending Email from my Access Database to update the users

I have a database application where we are tracking project information for a system that we develop and import data for. This database that I created is tracking how much time it takes us to load data into another system, the stats like # of records, etc.

We assign a data load to a particular employee by a field called Assigned To. I have a combobox with all of the employees working on this project.

How can I setup an email message that is sent when the person is selected to notify them?

We are using Exchange Server. They are in the GAL.

Thanks.
0
lauriecking0623
Asked:
lauriecking0623
  • 15
  • 13
  • 12
  • +1
2 Solutions
 
fhlio_adminCommented:
Determine the user information that will return the user in GAL (sTO_EMAIL) and write the summation of the data in HTML (sBODY) and pass to this function.

Function SEND_EMAIL(sTO_EMAIL As String, sHDR As String, sBODY As String)
On Error GoTo Handle_Error
Dim olAPPL As Outlook.Application, olMAIL_ITEM As Outlook.MailItem
Dim olACCT As Outlook.Account

Set olAPPL = New Outlook.Application
Set olMAIL_ITEM = olAPPL.CreateItem(Outlook.olMailItem)
'olMAIL_ITEM.Display

olMAIL_ITEM.Recipients.Add sTO_EMAIL
olMAIL_ITEM.Recipients.ResolveAll
olMAIL_ITEM.Subject = "Header Info"
olMAIL_ITEM.BodyFormat = olFormatHTML
olMAIL_ITEM.HTMLBody = sBODY
olMAIL_ITEM.Send

Set olAPPL = Nothing

End Function
0
 
lauriecking0623Author Commented:
@fhlio_admin

So I could add this function to my database; however, when you say that sBody will be the contents of the data in my database? I would want something like this to sent:

You have been assigned Data Load ID, Data Load Type, Case Number and Request ID, Data Path, Received Date.
0
 
fhlio_adminCommented:
Change the line in my original code...
FROM:
olMAIL_ITEM.Subject = "Header Info"
TO:
olMAIL_ITEM.Subject = sHDR

------------------------------------------------------------------------------------------------------


I will show you an example of those values being a local variable...

sBODY = "<p>You have been assigned:</p>"
sBODY = sBODY & "<p>Data Load ID: " & DataLoadID & "</p>"
sBODY = sBODY & "<p>Data Load Type: " & DataLoadType & "</p>"
sBODY = sBODY & "<p>Case Number: " & CaseNumber & "</p>"
sBODY = sBODY & "<p>Request ID: " & RequestID & "</p>"
sBODY = sBODY & "<p>Data Path: " & DataPath & "</p>"
sBODY = sBODY & "<p>Received Date: " & ReceivedDate & "</p>"
SEND_EMAIL "MyEmail", "My Email Header", sBODY)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TI2HeavenCommented:
As far as I know VBA has not a library to send email. But you can make use of third parties COM objects through GetObject or  CreateObject.
ASPMail for ASP classic is a very simple library to send emails (and is very cheap for one computer) and can be used for VBA.
It seems that your project it’s getting large, you should consider avoid using Access. Access should not be used on scenarios where many computers are sharing the same database at the same time. SQL Express is free.
¿GAL?
0
 
fhlio_adminCommented:
@TI2Heaven

I utilize the above code... It uses the reference of Outlook as lauriecking0623 had made reference to that.

Simply asking for an email process is not too "large" for Access.  VBA when utilized with other applications, including Outlook can be VERY powerful.
0
 
TI2HeavenCommented:
The solution given by fhilo... requires that people have Outlook installed in their desktops and its code will run outlook if is not loaded (you will see always outlook minimized). If Outlook is not well installed you will have a problem....
0
 
lauriecking0623Author Commented:
@T12Heaven,

I am using Access to start. It is not a HUGE database. I have already thought about upsizing it with SQL Express soon once we start adding records to the database.

@fhlio_admin

I have put the code into my database; however, would I need to do it on the dropdown menu for the Assigned To in the AfterUpdate event?
0
 
TI2HeavenCommented:
@fhlio... Access is for short projects, VBA is a child of Visual Basic that microsoft has deprecated and is asking to use VB.NET instead. ¿what do you thing about making new projects that requires VBA?
0
 
TI2HeavenCommented:
You will need to drop your project and start again when you make the switch; it is up to you how much code you want to drop.
0
 
lauriecking0623Author Commented:
@T12Heaven,

I do not expect this application to have over 2 GB in datasize. I used Access since I could use it without SQL right now.  

If we need to make this a more robust of an application, i would continue to use the front-end in Access and SQL as my backend for the data.  

I just want to be able to send this assignment email to the person that is assigned to the data load.  We all have Outlook installed on our systems.

Just trying to get this to work for this system. I am trying to really impress my contract job supervisors since I want this to be a permanent position.
0
 
TI2HeavenCommented:
When a leave using Access "SELECT statements" where not compatible with SQL server.
The change you mention will require dropping all your "SELECT statements".
Front in Access is ok but with the restriction of not using VBA.
Windows Forms with visual studio express is giving most of Access shortcuts for rapid development except for Reports.
With Visual studio express you develop in VB.NET language which has much more features than VBA.
0
 
fhlio_adminCommented:
@TI2Heaven
what do you thing about making new projects that requires VBA?

This is not applicable to this question.  I would be happy to discuss this matter offline, but let's keep the conversation on the question at hand.  This is the asker's dime.

@lauriecking0623

It depends on how you want it to work.  Personally, I would allow the user (you or another user) to select the name and then use a command button to actually kick off the information.  This allows the end user to make a "goof" on the issue.

@TI2Heaven / @lauriecking0623
I have been professionally developing MS Access since 1999.  Two things I have from my experience:
1.  Know what Access can and can't do.  Access is many times effecient for front-end use.  Even if you are dealing with millions of records.  It depends on how you use the tool.
2.  Never represent more than you are.  Typically this spells disaster for the person coming in behind you.  If you don't know ask.  EE makes a great forum for this.
0
 
TI2HeavenCommented:
@fhlio... I am agree with you, question should be closed. Your answer is great as well as mine.
I didn't mean to make a debate; I only wanted to help new developers to achieve criteria to select the right technology.
0
 
Jeffrey CoachmanCommented:
TI2Heaven,

Just a note here, as I see that you are a relatively new member.
(...and have already answered some fairly sophisticated question)

This question was originally assigned to the MS Access zone.
This means that all parties involved (the asker and the experts) are aware of the advantages and disadvantages of using MS Access.
A change in "development technology" is not what most people posting in the Access zone are interested in.
Especially not a jump as huge as going from VBA to .Net.
;-)

Typically new experts will stick to zones where there expertise is more targeted.
So if your specialty is .Net, Servers, SQL, ...then you may prefer to stay focused on those  zones.

So it's kinda like a Mercedes Benz Expert answering a question in the 57 Chevrolet zone, ...and the first thing they say is:
"Carburetor technology is old, and has been replaced by Fuel injection. ...You may want to look into purchasing a Mercedes Benz"
;-)

The reason a person is in the 57 Chevrolet zone is because the want help with a 57 Chevrolet carburetor issue.
So while it is always helpful to suggest alternate methods or technologies, ...in this case, the technology already in place (Access VBA) is full capable of doing what the asker wanted.
Sound fair?

Going with .net would require a huge investment in; new software, Time, money, ...etc

I wish you continued success here as an expert.
;-)

JeffCoachman
0
 
lauriecking0623Author Commented:
@JeffCoachman,

Thank you for your comments. I felt that it became an argument between the experts. I am looking for answers since I am not a programmer so it helps me to understand how to do this since I do like Access.  I got a sort of answer but then everything took off and being told to delete this question.
0
 
TI2HeavenCommented:
@JeffCoachman I really had a good time reading your post.  I agree with you in all except for “huge jump” (People still programing in VB.NET as if they were programming in Visual Basic). It is not the first time I give suggestions no one is asking, and I always get punch from them. But sometime I just can’t keep myself from doing it. Good intention does not change wrong acts to right acts. Sorry.
0
 
fhlio_adminCommented:
@lauriecking0623

I believe that when the conversation got off track, I tried to steer it back on track while continuing the clarification of your question.  If you received your answer, would it not be appropriate to close the question and award points rather than deleting it?
0
 
lauriecking0623Author Commented:
I would normally close it but I do not feel I got my question answered since I have an issue with the code that I was using. I will be posting something again.
0
 
fhlio_adminCommented:
Fair enough then.
0
 
TI2HeavenCommented:
@lauriecking0623
I suspect that you are trying  @fhlio_admin ‘s soluction and I am not sure if my comments are welcome but just in case.
You need to check you can send email through outlook manually before you can try your code.
Your application will use user account to send de items and users will have all the messages sent through the application in their sent items try.
If you prefer to use just one sender for all the emails sent through the application my solution is preferable.
0
 
lauriecking0623Author Commented:
@T12Heaven,

I have put in the code for @fhlio_admin's solutions. I have outlook that allows us to send emails to each other. I have five people that will get these emails only when they are selected as the Assigned person for the process. I am trying to get this to work. I am keeping this open until I can get it to work. Thank you.
0
 
lauriecking0623Author Commented:
@fhlio_admin

Ok, I think I did this wrong with associated the Function. Should I create a module basAssignments for the Outlook then on the After Update would have to execute the code?
0
 
TI2HeavenCommented:
OK. It is not a problem of instalation then. Your close to get your solution!
Thanks for the clarification
0
 
fhlio_adminCommented:
Yes, the function that I created will need to go in a standard module.  This code is an example of what you will use to call it.

sBODY = "<p>You have been assigned:</p>" 
sBODY = sBODY & "<p>Data Load ID: " & DataLoadID & "</p>"
sBODY = sBODY & "<p>Data Load Type: " & DataLoadType & "</p>"
sBODY = sBODY & "<p>Case Number: " & CaseNumber & "</p>"
sBODY = sBODY & "<p>Request ID: " & RequestID & "</p>"
sBODY = sBODY & "<p>Data Path: " & DataPath & "</p>"
sBODY = sBODY & "<p>Received Date: " & ReceivedDate & "</p>"
SEND_EMAIL "MyEmail", "My Email Header", sBODY

Open in new window


 @lauriecking0623

Concerning where you kick the code off at (AfterUpdate or elsewhere), it depends on how you want it to work.  Personally, I would allow the user (you or another user) to select the name and then use a command button to actually kick off the information.  This allows the end user to make a "goof" on the issue.

Shall I assume that we are no longer deleting this question?
0
 
lauriecking0623Author Commented:
I created a new module named basSendEmail. I did not declare any function libraries initiatial. I am very new to the VBA programming but starting to understand what I am doing.

I put a new command button on my form that is called Send Assignment (cmdSendAssignment) Now, I am trying to write the code that would run this function but having an issue to come up with it. Any suggestions??
0
 
TI2HeavenCommented:
Attach a file with all code....(Copy paste to notepad and send)
We wiil ask you for more things when look at your source code....
0
 
lauriecking0623Author Commented:
@T12Heaven,

I have attached a text file with the code per your request.
SendEmailCodeforAccess.txt
0
 
fhlio_adminCommented:
WAIT!!  That code in the text file will send an infinite loop of emails...
0
 
lauriecking0623Author Commented:
@fhlio_admin

This is the code that you gave me. I am trying to get execute just once after selecting the person that it is assigned to or by pushing a button to assign the data object process to them with an email as it states in the code.
0
 
fhlio_adminCommented:
You need to ensure that it is set in the OnClick property of the button.  You can just right click on the button and select "Build Event" and then select the "Code" option.  After you could write something like this...

Private Function cmdSendAssignment()
Dim sBODY as String

sBODY = "<p>You have been assigned:</p>" 
sBODY = sBODY & "<p>Data Load ID: " & DataLoadID & "</p>"
sBODY = sBODY & "<p>Data Load Type: " & DataLoadType & "</p>"
sBODY = sBODY & "<p>Case Number: " & CaseNumber & "</p>"
sBODY = sBODY & "<p>Request ID: " & RequestID & "</p>"
sBODY = sBODY & "<p>Data Path: " & DataPath & "</p>"
sBODY = sBODY & "<p>Received Date: " & ReceivedDate & "</p>"
SEND_EMAIL cboEmailAddress.Value, "Your Assignment", sBODY

Msgbox "Email sent."

End Function

Open in new window


The code below is what the standard module should look like.

Option Compare Database
Option Explicit

Function SEND_EMAIL(sTO_EMAIL As String, sHDR As String, sBODY As String)
On Error GoTo Handle_Error
Dim olAPPL As Outlook.Application, olMAIL_ITEM As Outlook.MailItem
Dim olACCT As Outlook.Account

Set olAPPL = New Outlook.Application
Set olMAIL_ITEM = olAPPL.CreateItem(Outlook.olMailItem)
'olMAIL_ITEM.Display

olMAIL_ITEM.Recipients.Add sTO_EMAIL
olMAIL_ITEM.Recipients.ResolveAll
olMAIL_ITEM.Subject = sHDR
olMAIL_ITEM.BodyFormat = olFormatHTML
olMAIL_ITEM.HTMLBODY = sBODY
olMAIL_ITEM.Send

Set olAPPL = Nothing

End Function

Open in new window

0
 
TI2HeavenCommented:
This is just a function, I think fhlio will need all.
I need to see in which event you called.
(I need to trace the call)
Do you know you can debug?
0
 
TI2HeavenCommented:
I am not sure but I belive that when you customize a event of a control of a form, you can see the code.....
0
 
TI2HeavenCommented:
Sorry @fhlio I miss understood one of your comments because of the response of @lauriecking0623.

@lauriecking0623,
You cannot call the function in the body of the function. (You can, but not in the way you do it).
Maybe It would be easier to send us the access file so we can see what you misunderstood
0
 
fhlio_adminCommented:
Based on my comment from 38343171....

Line 5 of the second code should be deleted.  This is part of an error routine that is not applicable in this question.  It will error in compiling as Handle_Error will not be found.
0
 
TI2HeavenCommented:
I fell you don’t need my help anymore; @fhlio_admin is doing a very good job.
Please do not mark me as an assisted answer because @fhlio_admin’s work affords all the points.
If you want me you can reach me in any of my open comments.
0
 
lauriecking0623Author Commented:
Ok, I guess the best way to handle this question is to restate my question again so I can get back onto the same page as everyone else:

I have a database application that we are using to track information about a project that we are working on for a client.  We have a software system that we are importing Data Loads into it. This database tracks all of the pertinent information that we need to report back to our client along with tracking in the process.
Load forms, I have a combobox where I have the values populated from my Employees
I have a table where I have the data loaders name and email address.  Within our Data Table of their names.  

I am trying to create code where a message is generated automatically and sent to the person that has been assigned the data load to perform.  Within that email, I want to have it have some of the important fields from that form.

How can I create a good code that will allow me to do this? I know it is possible; however, I am a novice VBA programmer.
0
 
fhlio_adminCommented:
Create a command button by the name of cmdSendEmail.

Right click the command button.  Select "Build Event".  Select "Code Builder".  That will take you to code that looks like this:
Private Sub cmdSendEmail_Click()

End Sub

Open in new window


Add code to make it look like this:
Private Sub cmdSendEmail_Click()
Dim sBODY as String

sBODY = "<p>You have been assigned:</p>" 
sBODY = sBODY & "<p>Data Load ID: " & DataLoadID & "</p>"
sBODY = sBODY & "<p>Data Load Type: " & DataLoadType & "</p>"
sBODY = sBODY & "<p>Case Number: " & CaseNumber & "</p>"
sBODY = sBODY & "<p>Request ID: " & RequestID & "</p>"
sBODY = sBODY & "<p>Data Path: " & DataPath & "</p>"
sBODY = sBODY & "<p>Received Date: " & ReceivedDate & "</p>"
SEND_EMAIL cboEmailAddress.Value, "Your Assignment", sBODY

Msgbox "Email sent."

End Sub

Open in new window


Create a standard module named "basAssignments".  The whole module should look like this:
Option Compare Database
Option Explicit

Function SEND_EMAIL(sTO_EMAIL As String, sHDR As String, sBODY As String)
On Error GoTo Handle_Error
Dim olAPPL As Outlook.Application, olMAIL_ITEM As Outlook.MailItem
Dim olACCT As Outlook.Account

Set olAPPL = New Outlook.Application
Set olMAIL_ITEM = olAPPL.CreateItem(Outlook.olMailItem)
'olMAIL_ITEM.Display

olMAIL_ITEM.Recipients.Add sTO_EMAIL
olMAIL_ITEM.Recipients.ResolveAll
olMAIL_ITEM.Subject = sHDR
olMAIL_ITEM.BodyFormat = olFormatHTML
olMAIL_ITEM.HTMLBODY = sBODY
olMAIL_ITEM.Send

Set olAPPL = Nothing

End Function

Open in new window


In the end, you will select the user, and hit the command button to kick the email out.
0
 
lauriecking0623Author Commented:
@fhlio_admin

I have implementing the code that you were great to give me; however, when I ran it comes back to the following:

Compile Error: User-defined type not defined
 Highlights the olAPPL As Outlook.Application in the debugger.

Do you know what it is referring to?
0
 
fhlio_adminCommented:
Yes.  You might have to break the code to make the change to fix this.

In the VBA window, go to Tools -> References.

In the list, find "Microsoft Outlook xx.0 Object Library".  The xx is the version that you have.  Likely, it will be 11-14.  It will NOT say 2003/2007/etc.

Check the references and hit the "OK" button.
0
 
lauriecking0623Author Commented:
I just got it to work. It is perfect!!!!!!!!! Thank you so much!!!!!!!!
0
 
TI2HeavenCommented:
What a good news!!!!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 15
  • 13
  • 12
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now