Email

[lynsey2]

Right guy's i gotta problem! cant seem to work it out even though Hay posted examples.

I have a DB with

tblContacts

ContactID
Email
CompanyName
ContactName
Tel
TYPE
WebAddress


frmADDREMOVE which is a data entry form so they can add new contacts to the db or delete contacts from it

frmSelect2Emailthis is a form with a combo box on it that allows them to select from the TYPE field in tblContacts. On selecting a certain TYPE the subform on this form shows all the different contacts that match the type chosen.

Splash the start up screen

TblContacts subform1 has 3 fields email, company name and type.

i need frmSelect2Email to have a button on it that launches outlook express, filling the Bcc section of outlook with all the UNSELECTED email addresses in the subform.

UNSELECTED emails only because the compay may select TYPE construction and 50 different contacts appear in the subform they may want to email 49 of them but not 1 so if they select the one they dont want then thats not bcc 'ed

if you know what i mean! here can you tell im back eh!!

hope you can help and here is what i have so far! ill post this first then the db in a sec

Lyns XXX

I AM EDITING THIS TO LET YOU KNOW WE SOLVED THIS PROBLEM AND I HAVE ATTACHED THE WORKING SOLUTION ON PG 6... READ THROUGH THIS THOUGH....EVEN IF IT IS ONLY FOR A LAUGH.

 

[Hayley Baxter]

Hi Lyns

Ok I am slighty confused as to why you need to take over the unselected emails into the bcc section, is that necessary? Isn't it easier to select from your combo list for all matches that they want to email then return these in the subfrm, choose the cmdbutton and take this into the To section of outlook?

Like this example here:

Thread

Or did I misunderstand you completely

Edit: Ok on reading through that again I think I know what you're saying. You are returning a seclected category but may not want to email all of them....I finally get it

Hay

 

[lynsey2]

>....

here is the DB
ill look at that Hay! still dont get it but!!!!

TA! Lyns X

 

[Hayley Baxter]

I'll have a look at your db for you Lyns.

 

[lynsey2]

your a star honey! the sample keeps asking for a pop3 address and i dont know! i just want it to open outlook and copy the email addresses from the subform into Bcc.
your a star!! sorry! tried a data access page but that wasn't what they wanted then i tried a macro but thats rubbish too

 

[Hayley Baxter]

How were you planning on selecting them, by using a checkbox? Just a thought you could maybe add a checkbox to your table and have the query run only those emails that are NOT selected - is this maybe a solution?

 

[lynsey2]

what happens is, in frmSelect2Email there is the combo box... this has the list of all the types from tblContacts. (my client wants to email by TYPE to construction companies or IT companies etc...construction and IT being my TYPE) so you SELECT say IT from the combo and the subform displays all the contacts from tblContacts with IT as thier TYPE i want the button to email all of the email addresses in the subform, UNLESS i have selected one... the selected one has not to be emailed!

Is that any better???
basically they wont use the table the will just use the sub form to select random email addresses they DONT want the rest have to be sent.

oh right i get you now sorry...

i planned on selecting the ones i dont want by clicking on them in the sub form on frmSelect2Email??????????

 

[Hayley Baxter]

Ok let me have a bash at that and we'll see how it goes. I know how to do it with a checkbox, not sure about highlighting the records in the subfrm but I'll try.

 

[lynsey2]

checkbox would work fine too i suppose...as long as the check box was in the subform eh?? Hay your pure dead brilliant you are! my phone is working again too got a new one with the same no. its one of those picture thinggys... its great!

 

[lynsey2]

i have been reading all the email related things i found by doing a search but they all start with error msges. cant find any that havent got a clue how to start outlook. im getting send object but dont know how to use it!

 

[Mile-O]

I think you'd be better off scrubbing the subform and going with a listbox with Multi-Select set to Simple (not a reflection you, )

That way you can loop through the whole listbox quite easily and add the email addresses into a string, separated by a semi-colon. Then, using the DoCmd.SendObject, you can use the string in the bcc argument.

 

[lynsey2]

so far i can make a button start outlook and send a message to a set email address! worked that bit out with some of the things i read in here! still no further than that yet!

 

[Mile-O]

Have a look at this.

 

[lynsey2]

DoCmd.SendObject acSendTable, TblContacts, acFormatTXT,,,Forms!frmSelect2Email!TblContacts subform1!Email
end sub

 

[lynsey2]

im trying but everytime it asks for me to set up a new email account!??? here it is again 35 mins and counting!

 

[lynsey2]

thats just for one and not mass click on the text box to see that it keeps asking me to set up an email account... im not sure how to do the pop 3 bit either??

 

[Mile-O]

JUst so you know: I don't have Access 2k, XP, whichever you are using, and I've never bothered with Outlook more than the simple DoCmd.SendObject command.

What you could do is use DAO to go through the recordset that is in the subform and get the email addresses.

 

[lynsey2]

Hey we all know how good i am with access i dont have a clue.

if there is a simple answer to this question that would be great

in the on click event of my email button is there somehow i can get it to look at every single email address in the subform and stick it in the Bcc of outlook?

outlook is opening just now with no addresses so is it easy to tell it to open with all the addresses? I have 25 min.

i dont know how to make it open and put any of the addresses from the table into to: cc: or bcc:

 

[lynsey2]

Would this just about work?

Private Sub Command8_Click()
DoCmd.SendObject , , , Me.Form![TblContacts_subform1!Email], , , , , , True
ExitHere:
ErrorHandlerExit
End Sub

 

[Hayley Baxter]

Hi Lyns

sorry I've not got back to you, you would not believe the day I've had, I ended up being at work until 7pm tonight (yes as bad as that) anyhow if you use code similar to this you won't need to copy the addresses into the bcc section, instead you can take them into the to section as this code will look at each contact in turn and send them an email one by one until it reaches the end of the recordset so say for example you have 20 emails in your subform = 20 items sent in your sent items as opposed to 20 names appearing in your bcc section. I use this on a regular basis and it works fine.

It would look something like:

Private Sub CmdEmail_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("table/queryname")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject , , , strEmail, , , _
"Subject", _
"Good Morning / Afternoon" & _
vbCrLf & vbCrLf & "Message", False

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

MsgBox "Emails have been sent"
End Sub

Now this doesn't allow for an attachment but if you need this just add it on the end of the docmd.sendobject line and remember to include the file type you want to attach. See how you get on with that.

Hay