Email Fields - Auto Filling in from stored data

[AstroDonut]

Hi,

I have used DoCmd.SendObject to create an email with a report attached, I found a snippet of code to insert the unique reference into the 'Subject:' field on the email.

I tried playing around with it to solve my problem and it failed!!

So...

I have a table of users with an ID, first name, second name, email address

An autonumber is created using data from that table to create a unique reference for the record in a format put in place by our ISO fella.

I want to send an email to two people, the person in the 'To:' field stays the same so I put "samepersonsname@thecompany.com" in the vba, I want to put the name of the person creating the record related to the attached report in the 'CC:' box of the same email.

Does anybody understand what I'm asking?

I'm an extreme novice user with vba (I've copied and dabbled, just not fluent!!)

Thanks in anticipation

Bri

 

[Scooterbug]

Are you looking to put in the name based off of a record in a table, or do you want a message box to pop up with a place to manually put in the person's email?

The Input box is easier to explain without knowing your table structures.

Dim sInputBox as String
 
sInputBox = Inputbox("Please enter the additional email address:") & ";"

Then, in the line where you specifid the static email address, add this to the end of it:

& InputBox

I would also add a ; to the end of the static email address as well. (Multiple recepiants require a ; between the email addresses)

 

[AstroDonut]

Hi,

Using vba I've managed to get the initial letters of the person creating a record onto the unique reference

the unique reference is like this ->> T-1009/10/BW

BW is the initials of the user creating the record, this reference is related to "Bob Walker" in tblUser, also in tblUser is a field with "bobwalker@company.com", there are more users in the table, John Smith would create T-1009/11/JS and so on...

If Bob Walker creates the record then I want bobwalker@company.com inserting into the cc field.

The idea is to make this application as user friendly and requiring little input from the person, only what they used to do on the hand written version

I don't think I'm making much sense!!

 

[Scooterbug]

Hi,

Using vba I've managed to get the initial letters of the person creating a record onto the unique reference

the unique reference is like this ->> T-1009/10/BW

BW is the initials of the user creating the record, this reference is related to "Bob Walker" in tblUser, also in tblUser is a field with "bobwalker@company.com", there are more users in the table, John Smith would create T-1009/11/JS and so on...

If Bob Walker creates the record then I want bobwalker@company.com inserting into the cc field.

The idea is to make this application as user friendly and requiring little input from the person, only what they used to do on the hand written version

I don't think I'm making much sense!!

I understand what you are looking to do. What you want is a Dlookup. It would look something like this:

Dim sSecondToField as String
 
sSecondToField = DLookup("[EmailAddressFieldName]","tblUsers", & _
"[UniqueFieldName]"= & Chr(34) & UserUniqueField & chr(34)) & ";"

Then you can set the value of the cc field to sSecondToField.

If the unique identifier is on a form, you can reference it like this:

Forms!YourFormNameHere!UniqueIdControlName

 

[AstroDonut]

Scooterbug? You are a legend

I managed to dabble with your suggestion

sSecondToField = DLookup("[EmailAddressFieldName]","tblUsers", & _
"[UniqueFieldName]"= & Chr(34) & UserUniqueField & chr(34)) & ";"

became

sSecondToField = DLookup("[EmailAdd]", "qryCompLogForReport")

Report was based on that qry

I put 'sSecondToField' in the cc bit of the sendobject

Created a couple of records with different users and it worked

I can't thank you enough

Bri