Send Email Conditions

johndohnal

Registered User.
Local time
Today, 17:33
Joined
Sep 12, 2013
Messages
41
I would like to send an email notification "On Insert" when a new record is entered. In the Body of the email, I'd like to include an "Item Name" that is associated with the record. The table I'm using has an "ItemID" field, which is numeric. This is easy enough to include as it comes from the same table. The actual "Item Name" comes from a linked field in a different table. How can I get the actual "Item Name" to appear in the Body of the email?
Thanks,
John
 
I'm guessing the ItemID field is behind a combobox, that shows ItemName from a later column?

You can reference the unbound columns with syntax like Me.comboItems.Column(X), remembering that X starts at 0, not 1. Test in the VBA Immediate window to make sure you've got the right column number, if necessary.
 
Hi David R, looks like you're my new best friend! I've used this before in text boxes to reference unbound columns from a combobox, but I'm not sure how I would do that from within the send email macro. Could you provide more specific instructions?
Thanks,
John D
 
I never use Macros, so I'm going to be of limited help here. Can you post a screenshot of your Macro as it stands now? Particularly the part where it builds the Body, if it's a long macro...
 
I've attached a screenshot below. This works fine except that I get a numeric value instead of text. "24 is ready for QA" instead of "Cryosolve is ready for QA". Thanks for you time and effort!
Thanks,
John D
 

Attachments

  • Send Email Screenshot.png
    Send Email Screenshot.png
    15.4 KB · Views: 162
Is tblTransfer a table or a form? I hate Macros... (not your fault)

Anyway, if this is a form you just need
Code:
Subject = [Form].[comboMaterialSupply].Column(X) & " is ready for QA"

If it's a table you can should be able to do it with a dlookup, but it will run somewhat slower.
Code:
Subject = DLookup("fldMaterialSupplyName","tableMaterialSupply","fldMaterialSupplyID = " & [tblTransfer].[MaterialSupplyID]) & " is ready for QA"
Fix field and table names as appropriate.
 
Hi David,

This worked perfectly! Thanks for your help!

John D
 
Sorry David, I spoke too soon. This only sends the MaterialSupply from the first record, not the record which was just updated. Any suggestions?
 
That's odd. How does your Macro differ from my #6?
 
Code:
Subject = DLookUp("MaterialSupply","tblMaterialSpecifications","MaterialSupplyID=" & [tblReceipt].[MaterialSupplyID]) & " is ready for QA"
It looks the same to me, but it only grabs the first MaterialSupplyID. Since this is a Data Macro, it has to run from a table. I tried creating a named data macro and call it from the form, but it didn't do anything at all. I need a way to have it grab the MaterialSupplyID for the most recent record insertion.


Thanks for you help!
John D
 
Are you CERTAIN it will be the most recent record insertion? What if you have more than one user in the system that day?

Anyway, sounds like you need to add an additional criterion that identifies that most recent record for the system, and spits out that record every time. It'll probably be slower still, but something like
Code:
Subject = DLookUp("MaterialSupply","tblMaterialSpecifications","MaterialSupplyID=" & _
   [tblReceipt].[MaterialSupplyID] & " AND [I][COLOR="Red"]MaterialSupplyLastUpdated[/COLOR][/I] = #" & _
   DMax([tblReceipt].[[COLOR="Red"][I]MaterialSupplyLastUpdated[/I][/COLOR]],"tblMaterialSpecifications") & "#") & _
   " is ready for QA"
Obviously I don't know the name of that field, so I put it in red. You can play with DMax and DLookup in the VBA Immediate window, just put a ? in front of your 'code' to see what it's spitting out until you get the right answer.

You can avoid the DMax dance if you have a field you can just point to and say 'THIS identifies the most recent record, which I have just completed' for example, but I don't know the data structure of your database as well as you do. I'm frankly a little confused how we got here from the original question, so if you can shed some light, go for it...
 

Users who are viewing this thread

Back
Top Bottom