Need help doing the following: 1

You have to play with VBA and RecordSet

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

PHV,

I've been researching Recordset since your post. however, I'm afraid i'll need more help and guidance to resolve my problem.

thanks

Have you already figured out how you will send email ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

Yes I have. I found a thread that explained how to do it using Lotus notes. That's what got me thinking about converting the email table.

I have it setup right now to use one specific email address that can be changed. However, if I want to send the file to more then 1 recepient, I have to repeat the email function as many times as I have email addresses.

by the way, here is the thread I found to link lotus notes to access.

181-310363

Can you please post your email function to see how we can implement the list ?
Do you want to spam everyone in the email table or only names checked in a MultiSelect ListBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

Thanks for your help. I don't want to spam at all. There could be a max of 10 recipients at this time.

here is the code.

Option Compare Database
Option Explicit

Private Declare Function apiFindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Private Declare Function apiSendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal msg As Long, ByVal _
wParam As Long, lParam As Long) As Long

Private Declare Function apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal Hwnd As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias _
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function apiIsIconic Lib "user32" Alias _
"IsIconic" (ByVal Hwnd As Long) As Long

Function SendNotesMail(strTo As String, strSubject As String, strBody As String, strFilename As String, ParamArray strFiles())
Dim doc As Object 'Lotus NOtes Document
Dim rtitem As Object '
Dim Body2 As Object
Dim ws As Object 'Lotus Notes Workspace
Dim oSess As Object 'Lotus Notes Session
Dim oDB As Object 'Lotus Notes Database
Dim X As Integer 'Counter
'use on error resume next so that the user never will get an error
'only the dialog "You have new mail" Lotus Notes can stop this macro
If fIsAppRunning = False Then
MsgBox "Lotus Notes is not running" & Chr$(10) & "Make sure Lotus Notes is running and you have logged on."
Exit Function
End If

On Error Resume Next

Set oSess = CreateObject("Notes.NotesSession")
'access the logged on users mailbox
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL

'create a new document as add text
Set doc = oDB.CREATEDOCUMENT
Set rtitem = doc.CREATERICHTEXTITEM("Body")
doc.sendto = strTo

doc.Subject = strSubject
doc.Body = strBody & vbCrLf & vbCrLf

'attach files
If strFilename <> "" Then
Set Body2 = rtitem.EMBEDOBJECT(1454, "", strFilename)
If UBound(strFiles) > -1 Then
For X = 0 To UBound(strFiles)
Set Body2 = rtitem.EMBEDOBJECT(1454, "", strFiles(X))
Next X
End If
End If
doc.SEND False




End Function

Sub test()
Dim strTo As String 'The sendee(s) Needs to be fully qualified address. Other names seperated by commas
Dim strSubject As String 'The subject of the mail. Can be "" if no subject needed
Dim strBody As String 'The main body text of the message. Use "" if no text is to be included.
Dim FirstFile As String 'If you are embedding files then this is the first one. Use "" if no files are to be sent
Dim SecondFile As String 'Add as many extra files as is needed, seperated by commas.
Dim ThirdFile As String 'And so on.
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim strCc As String
Dim strBcc As String


Set db = CurrentDb

' query extracting email addresses,
' or SQL statement to do the same
Set rec = db.OpenRecordset("qryemail", dbOpenSnapshot)

strTo = rec!Email


strSubject = "Monthly LP Metrics file"
strBody = "The attached is the monthly LP Metrics file."
strBody = strBody & vbCrLf & "Please Detach this file in C:\Files\"
strBody = strBody & vbCrLf & "You will then be able to import the data from the DB import menu"
FirstFile = "C:\Files\Monthly LP Metrics.xls"
SecondFile = ""
ThirdFile = ""

SendNotesMail strTo, strSubject, strBody, FirstFile, SecondFile, ThirdFile
End Sub
Sub email2()
Dim strTo As String 'The sendee(s) Needs to be fully qualified address. Other names seperated by commas
Dim strSubject As String 'The subject of the mail. Can be "" if no subject needed
Dim strBody As String 'The main body text of the message. Use "" if no text is to be included.
Dim FirstFile As String 'If you are embedding files then this is the first one. Use "" if no files are to be sent
Dim SecondFile As String 'Add as many extra files as is needed, seperated by commas.
Dim ThirdFile As String 'And so on.
Dim db As DAO.Database
Dim rec As DAO.Recordset

Set db = CurrentDb

' query extracting email addresses,
' or SQL statement to do the same
Set rec = db.OpenRecordset("qryemail2", dbOpenSnapshot)

strTo = rec!Email
strSubject = "Monthly Regional SPR Results"
strBody = "The attached is the monthly Regional SPR Results."
strBody = strBody & vbCrLf & "Have a Great Day!"
strBody = strBody & vbCrLf & ""
FirstFile = "C:\Files\Monthly SPR results.xls"
SecondFile = ""
ThirdFile = ""

SendNotesMail strTo, strSubject, strBody, FirstFile, SecondFile, ThirdFile
End Sub

Private Function fIsAppRunning() As Boolean
'Looks to see if Lotus Notes is open
'Code adapted from code by Dev Ashish

Dim lngH As Long
Dim lngX As Long, lngTmp As Long
Const WM_USER = 1024
On Local Error GoTo fIsAppRunning_Err
fIsAppRunning = False

lngH = apiFindWindow("NOTES", vbNullString)

If lngH <> 0 Then
apiSendMessage lngH, WM_USER + 18, 0, 0
lngX = apiIsIconic(lngH)
If lngX <> 0 Then
lngTmp = apiShowWindow(lngH, 1)
End If
fIsAppRunning = True
End If
fIsAppRunning_Exit:
Exit Function
fIsAppRunning_Err:
fIsAppRunning = False
Resume fIsAppRunning_Exit
End Function

Sorry for the bad wording (I'm not a native english speaker).
The question was: How are the sendees selected ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

No problem!! in case thios could help you, I speak both english and french (I write much better in english).

The sendees are selected with he following query:
Set rec = db.OpenRecordset("qryemail", dbOpenSnapshot)

and then looks in the Email field. The problem is that it only finds the first record, not the other 9.

I was hoping to find a way to select all 10 emails (which are stored as records in a table) and be able to somehow list them one after the other with a coma.

Of course I had not thought of selecting names checked in a MultiSelect ListBox. that would be a very nice option!
(instead of selecting all 10 users) this would increse flexibility.

Hope that answers your question.

So create a ListBox, say lboEmail.
MultiSelect: 1 or 2
ColumnCount: 2
BoundColumn: 2
RowSourceType: Table/Query
RowSource: SELECT A.name, A.email FROM email ORDER BY 1;

In the Click event procedure of a SendToList button:
strTo = ""
For Each varItem in lboEmail.ItemsSelected
strTo = strTo & ";" & lboEmail.ItemData(varItem)
Next varItem
strTo = Mid(strTo, 2)

Voila, the send to list is build.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

I created the list box, all is good. I'm having difficulty with where to put the following as I don<t have a SendToList button.

In the Click event procedure of a SendToList button:
strTo = ""
For Each varItem in lboEmail.ItemsSelected
strTo = strTo & ";" & lboEmail.ItemData(varItem)
Next varItem
strTo = Mid(strTo, 2)


Can I put the above code where the strTo line is below?
When I do, I get an error message "Variable not define"

please note that I have put the list box in a form named "export"
Is there a way to have the below code refer to the list box?

Set db = CurrentDb

' query extracting email addresses,
' or SQL statement to do the same
Set rec = db.OpenRecordset("qryemail", dbOpenSnapshot)

strTo = rec!Email


strSubject = "Monthly LP Metrics file"
strBody = "The attached is the monthly LP Metrics file."
strBody = strBody & vbCrLf & "Please Detach this file in C:\Files\"
strBody = strBody & vbCrLf & "You will then be able to import the data from the DB import menu"
FirstFile = "C:\Files\Monthly LP Metrics.xls"
SecondFile = ""
ThirdFile = ""

PHV,

Thank you very much for your help and guidance, I have all figured out!!

martin