Passing data from query to multidimensional string array

[anb001]

Ini my 'quest' to find out and learn how to send emails from my database, I received great help to loop through the code, and send the email to multiple recipients, but with one email per recipient, in stead of one email for all recipients.

I have been told that I need to use multidimensional string arrays for doing so, as the mail program (Groupwise) won't accept one long string of email addresses.

What I'm looking for is a way to pass the data from a query, which contain two fields (one is Names and the other Email_addresses), into this multidimensional string array.

And also how should the multidimensional string array be configured in vba? In the example code below, three names/emails can be added. Should I create the string array as "strRecTo(1, 31)", and can that be changed when running the code, so it is dependent on number of emails in the query??

Private Sub cmdSendMail_Click()
On Error GoTo Err_Handler

Dim filename As String
Dim rptname As String
Dim strTemp As String
Dim strRecTo(1, 2) As String
Dim lngCount As Long
Dim varProxies As Variant
Dim cGW As GW


If DLookup("[UseNewDate]", "[qryPath]") = True Then
    filename = DLookup("[FilePath]", "[qryPath]") & "Daily Report " & DLookup("[NewDate]", "[qryPath]") & ".pdf"
    'work out where the user wants to save the file!
Else
    filename = DLookup("[FilePath]", "[qryPath]") & "Daily Report " & Date & ".pdf"
End If

'setup the report print variable
rptname = "rptDailyReport" ' you could have something here that lets the user choose from all the reports

'call the Lebans PDF creating module
Call ConvertReportToPDF(rptname, vbNullString, filename, False, False, 150, "", "", 0, 0, 0)


strRecTo(0, 0) = "email_1@test.com"
strRecTo(1, 0) = "Test name 1"
strRecTo(0, 1) = "email_2@test.com"
strRecTo(1, 1) = "Test name 2"
strRecTo(0, 2) = "email_3@test.com"
strRecTo(1, 2) = "Test name 3"

'etc etc


Set cGW = New GW
With cGW
  .Login
  .BodyText = [txtEmailBody]
  .Subject = [txtEmailSubject]
  .RecTo = strRecTo
  .FileAttachments = filename 
  .FromText = "me@mail.com"
  .Priority = "Normal"
  strTemp = .CreateMessage
  .ResolveRecipients strTemp
  If IsArray(.NonResolved) Then MsgBox "Some unresolved recipients."
  .SendMessage strTemp
  .DeleteMessage strTemp, True
End With


Exit_Here:
  Set cGW = Nothing

  Exit Sub

Err_Handler:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
  Resume Exit_Here

End Sub

 

[PeterF]

As groupwise seems to work different then Ootlook it needs a different approach.
Sample code for groupwise can be found here.

 

[anb001]

The code I'm using is also for Groupwise, and it works fine. The only problem is sending to multiple recipients.

As far as I can see from your linked example, multidimensional string arrays are also used (the "Dim TestEmails(1) As String" part), and some sort of passing emails from e.g. queries are also needed here.

 

[anb001]

I have searched around a bit, and noticed that using GETROW to retrieve a recordset, should create a two dimensional array? Is that correct?

When using it, I would need the "output" of the getrow to be a string (I guess), and use that string as the recipients when sending emails. I have added some code (marked with red), however it doesn't work. It just gives me the msgbox with "Some unsolved recipients", meaning the string is empty.

Can someone maybe let me know what needs to be changed?

Private Sub cmdSendMail_Click()
On Error GoTo Err_Handler

Dim filename As String
Dim rptname As String
Dim strTemp As String
Dim lngCount As Long
Dim varProxies As Variant
Dim cGW As GW
[COLOR="DarkRed"]Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim ardata

strSQL = "SELECT * FROM tblEmailAddress

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
rs.MoveLast

If Not rs.EOF Then ardata = rs.GetRows(rs.RecordCount)"[/COLOR]

Set cGW = New GW
With cGW
  .Login
  .BodyText = [txtEmailBody]
  .Subject = [txtEmailSubject]
  .RecTo = [COLOR="darkred"]ardata[/COLOR]
  .RecCc = [txtEmailCC]
  .FileAttachments = filename
  .FromText = "me@mail.com"
  .Priority = "Normal"
  strTemp = .CreateMessage
  .ResolveRecipients strTemp
  If IsArray(.NonResolved) Then MsgBox "Some unresolved recipients."
  .SendMessage strTemp
  .DeleteMessage strTemp, True
End With

    

Exit_Here:
  Set cGW = Nothing

[COLOR="darkred"]rs.Close
Set rs = Nothing
Set db = Nothing[/COLOR]

  Exit Sub

Err_Handler:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
  Resume Exit_Here

End Sub