Store values in avariable and use it as To for Outlook

[Asghaar]

Hello all,

Here is the situation i confront with and need your help:
- I have different forms that use to manage different information's (like countries contacts,technical team's contacts etc)
- the details regarding this contacts are shown in a datasheet subform found in each main form
- in this subform(datasheet) i have the e-mail adsress hyperlinked and with a click event that copies that address mail (clicked) to a textbox found on the main form (where the subform is as well)
- this type of "structure" that i build is found in all the main forms ( main form/subform/email field hyperlinked with click event to copy it to that main form textbox the user is )

What i want to do now is to unify all this textboxes that may contain the e-mail addresses copied by the users in the different forms they were and have a in every form to have a textbox that will show the exact same addresses found in the unified one.
So when the user clicks the hyperlinked email,this to be copied in the unified textbox i created and all the other textboxes to show all the values that are in this unified textbox.

I found a solution:
- first to create another form with a textbox and all the clicked e-mails to be copied here (already managed to do this to work with setfocus to that form and after to textbox and copy there with setfocus back to the form were the email was clicked)
From here i have some code that uses that textbox as To to send outlook e-mail (this works fine)
But for this solution i must have a macro that opens this form(with the unified box) in order for the click event to find the unified textbox and copy to it + there is another window open in the taskbar -which i would prefer not to have

I think i should be able to find a more pleasant solution using a public variable or something like that to store all the emails that the users might click and use that variable as To value for Outlook.
But unfortunately although i searched for some information's to this i'm not able to do it.
Any advice or another solution so i don't have to use the form one?

Thanks for all the replies and sorry for the wall of text.

 

[MikeLeBen]

How about this:

- create a temp table;
- populate it with all the addresses;
- query it to be the rowsource of the box in your form for displaying purposes;
- delete it after the data has been exported to the "To:" field in the mail application?

 

[Asghaar]

Thank you MikeLeBen for your reply and suggestion,

The thing is that i use code to put the string which represents the value of the textbox as .to: for outlook application. If i do a table in which every row is independent -its his own string (something like that) how can i set the.to to take all the e-mail adresses from the table?
Also using dlookup i dont know how i cand show the result of the querry ,expecialy since it can contain more than 1 adress

This are the initial problems i can see myself having with this way of implementing.

 

[Asghaar]

Hello,
Thank your for your suggestion.
The thing is i don't know how i could use the entire table as To for outlook inside code since every row is seen separate.
Also with dlookup i can't show the entire query results - shows only one value

This are my problems with what you suggested.

 

[MikeLeBen]

You would use the Recordset Object in VBA to pull all the addresses and assemble them in one string.

See the following code for reference:

Private Sub cmdListEmails_Click()

Dim db          As DAO.Database
Dim rs          As DAO.Recordset
Dim mailinglist As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT MyEmailField FROM MyTable;")

Do While Not (rs.BOF And rs.EOF)
        mailinglist = mailinglist & rs.Fields(0) & ";" 'omit this last part if you already have semicolons
        rs.MoveNext
Loop

rs.Close

Debug.Print mailinglist

End Sub

It is not tested so you might need to fiddle with it a bit.

Also check the best resource when working with recordsets, Allen Browne's Website.

 

[Asghaar]

Hello MikeLeBen,

First let me thank your for your ideea and help.
I managed to have the click event add the records to my table (t1).
And with a got focus event on the textbox i "refresh" it with the recordset string from the table.
Works great.
If i try to put this 2 to parts of code on the same click event i get " Object required" error and dont know why - maybe you have an advice for this (the code is below). ==> i get the error but it works,the textbox is refreshed and shows the recordset string.
But the main problem is if the users deletes one of the addresses shown in the textbox,this will reaper as soon as a new one is added - i understand why ,because is not deleted from the table as well.
Do you have a suggestion for this?
Thank you in advance for all the help.

Private Sub E**ail_Click()
On Error GoTo myError
Dim varWhereClause As String
Dim sql As String


'Dim ctl As Control
'varWhereClause = "[E**il] = '" & Me![E**il] & "'"
varWhereClause = " " & Me![E**il] & ";"

sql = "Insert into t1 (mail) Values (""" & varWhereClause & """);"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

DoCmd.GoToControl "Text34"

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strString As String

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT mail FROM t1;")

With rst
Do Until .EOF
strString = strString & ![mail]
.MoveNext
Loop
End With
Text34.Text = strString
rst.Close



Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub

////the stars replace "ma" letters from the word,the forum doesn't allow me to add this word because it thinks i'm offering an e**ail address

 

[MikeLeBen]

Well wouldn't it be as simple as using an sql "delete" statement?

 

[Asghaar]

Hi,
Well the textbox that shows my recordset(which is based on the table that keeps my clicked e-mail adresses) allows the users to click in it and delete/change some adresses - which is fine.
But when the user deletes a record from the textbox this is not deleted from the table as well, so as soon as a new adress is added the textbox the record previously deleted will reappear in the textbox -because the deletion process is only local and doesn't affect the records from the table
I understand why this happens but don't know how I could keep this way of interacting with the textbox (is easier for the user) but also delete the proper fields that are missing in the textbox from the table.
How could i implement the delete sql command to keep this feature described?

Thank you

 

[MikeLeBen]

I think you may need a better record management interface if this is the case.

It would be wise to show the partial results on a listbox (the recordsource of which would be your temp table), and allowing the user to select records (i.e.: e-mail addresses) from it and possibly delete and/or update them by clicking a button.
You might want to use a pkID in your table to make things easier on the coding side.

 

[Asghaar]

hello,

I managed to find another solution:
- i replaced the textbox with a continuous subform ( which has the source as the table that holds my e-mail addresses)
- being a form i could set a delete button for each adress that shows up in it
offcourse this will dele te the record from the table as well
- and i use the recordset after the table for the To: Outlook
Works great.


Also i added an right click event on mousedown so the user can click to add TO or right click to add to CC

Right now i'm looking for a solution to solve the way the continous subform appear in the main form - i would like it to be empty without the delete button showing up when the record count is 0
Hope i will find how to do this.

But your solution and suggestion really helped me MikeLeBen solving the first crucial problems i had and for that thank you

 

[MikeLeBen]

No problem, glad you could solve it.

As for the button on the form_load event you could use a DLookup or recordcount to act on the .visible property of your button.

Private Sub Form_Load()

If Nz(DLookup("mail", "t1"), 0) = 0 Then
    Me.cmdDelete.Visible = False
Else
    Me.cmdDelete.Visible = True
End If

End Sub

Clearly, you want to run this check also when records are added subsequently so it won't stay hidden later on when email addresses are actually added.

 

[Asghaar]

Hello,

Worked like a charm - thank you a lot for all the help .

I will post my code for people that look simmilar things - all of it is tested and works (at least in 2000-2003 )

Private Sub Email_Click()
On Error GoTo myError
Dim varWhereClause As String
Dim sql As String

varWhereClause = " " & Me![Email] & ";"

sql = "Insert into t1 (mail) Values (""" & varWhereClause & """);"
 
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

Forms!T_all!t1_mail.Requery

If nz(DLookup("mail", "t1"), 0) = 0 Then
    Forms!T_all!t1_mail!Command3.Visible = False
Else
    Forms!T_all!t1_mail!Command3.Visible = True
End If

Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub

-the right click event that will add the e-mail adress to Cc field

Private Sub Email_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
On Error GoTo myError
Dim varWhereClause As String
Dim sql As String

If Button = 2 Then 'Right Click was clicked

varWhereClause = " " & Me![Email] & ";"

sql = "Insert into t2 (e_mail) Values (""" & varWhereClause & """);"
 
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True

End If

Forms!T_all!t2_mail.Requery


If nz(DLookup("e_mail", "t2"), 0) = 0 Then
    Forms!T_all!t2_mail!Command3.Visible = False
Else
    Forms!T_all!t2_mail!Command3.Visible = True
End If

Exit Sub
myError:
MsgBox Error$
Resume Next
End Sub

-to make each command invisible while the subform has no records
=first is on the command property
=second on the continuous form load event

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

If nz(DLookup("mail", "t1"), 0) = 0 Then
    Me.Command3.Visible = False
Else
    Me.Command3.Visible = True
End If

Exit_Command3_Click:
    Exit Sub

Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
    
End Sub

Private Sub Form_Load()
If nz(DLookup("mail", "t1"), 0) = 0 Then
    Me.Command3.Visible = False
Else
    Me.Command3.Visible = True
End If
End Sub

- the code for sending e-mail to outlook ( indifferent of the references access has)

Private Sub Command58_Click()
On Error GoTo Err_Command58_Click

Dim Email As String
Dim ccmail As String
'Email = Me!Text34

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim db As DAO.Database
Dim strString As String
Dim strString2 As String
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("SELECT mail FROM t1;")
Set rst2 = db.OpenRecordset("SELECT e_mail FROM t2;")
 
With rst
Do Until .EOF
  strString = strString & ![mail]
  .MoveNext
Loop
End With

With rst2
Do Until .EOF
  strString2 = strString2 & ![e_mail]
  .MoveNext
Loop
End With

Email = strString
ccmail = strString2
rst.Close

    'NOTE: Additional Const declaration
    Dim objOutlook As Object    'Outlook.Application  (Note dimensioned as Object)
    Dim objEmail As Object      'Outlook.MailItem     (Note dimensioned as Object)
    Dim objNameSpace As Object  'Outlook.NameSpace    (Note dimensioned as Object)
    Const olMailItem As Long = 0    'For Late Binding
    Const olFolderInbox As Long = 6 'For Late Binding
    Const olFormatHTML As Long = 2  'For Late Binding
       
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0     'Resume error trapping ASAP
   
    If objOutlook Is Nothing Then
        Set objOutlook = CreateObject("Outlook.Application")
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        objNameSpace.GetDefaultFolder(olFolderInbox).Display
    End If
   
    Set objEmail = objOutlook.CreateItem(olMailItem)
 
 
 With objEmail
        .To = Email
        .CC = "SSPO.Change;" & ccmail
        .Subject = " "
        '.save
        .Display
        '.Send
End With

Exit_Command58_Click:
    Exit Sub

Err_Command58_Click:
    MsgBox Err.Description
    Resume Exit_Command58_Click
    
End Sub

Regards,