Excel or Access Send Email Button

[Feriol]

I need to create a list of people, with a button to create an email to a selection of email address's for each person. Ideally this would get the list of emails to send to from an external text file (or seperate table in the database) with the name of the file recorded in another cell. E.G

Forename     Surname       Department                       Notes

Joe             Bloggs          PACS                  Off sick until 10/04/11
John            Smith           PACS                   
Jane            Doe             IT                      
ETC             ETC             ETC                     ETC

Next to each entry i would like a button that created an email To: (the emails listed in 'department cell'.txt) the subject being 'Forename Surname' and the body containing the contents of the 'notes' cell for that person

Is this possible, and how can it be achieved?

 

[Darth Vodka]

you ok looping through a table in Access VBA?

using the DAO recordset

 

[Feriol]

you ok looping through a table in Access VBA?

using the DAO recordset

No i'll have to research that. I'm trying to teach myself as i go.

 

[Darth Vodka]

here's an example of looping through a table


Sub LoopThroughTable
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT DISTINCT Series FROM tbl_data_Trades_Issue" & vbCr
strSQL = strSQL & "WHERE BB_ISIN Like '*" & strPartISIN & "*' OR IPA_ISIN Like '*" & strPartISIN & "*';"

Set rs = CurrentDb.OpenRecordset(strSQL)
Do Until rs.EOF
msgbox rs.Fields("Series").Value
rs.MoveNext
Loop
Set rs = Nothing

End Sub

 

[Darth Vodka]

and here's a procedure to send an email

'---------------------------------------------------------------------------------------
' Procedure : SendEmail
' Author : VodkaD
' Date : 28/01/08
' Purpose : generic email VBA for Access
' needs the outlook DLL to be loaded in Tools>Reference
' DV 28/01/08
'---------------------------------------------------------------------------------------
'
Sub SendEmail(strRecipient As String _
, strSubject As String _
, Optional strCC As String _
, Optional strBody As String _
, Optional strFilePath As String _
, Optional booReadReceipt As Boolean = False)

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = CreateObject("outlook.application")
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.Recipients.Add (strRecipient)
.Subject = strSubject
If strCC <> "" Then
.CC = strCC
End If
If strBody <> "" Then
.Body = strBody
End If
If strFilePath <> "" Then
.Attachments.Add strFilePath
End If
.ReadReceiptRequested = booReadReceipt
.Send
End With
Set olMail = Nothing
Set olApp = Nothing

End Sub

 

[Darth Vodka]

you starting in VBA for the first time?

 

[Feriol]

you starting in VBA for the first time?

Yeah, suppose i should buy a book, but i tend to learn better practically, so i'll have a go at disecting the above code, see if i can make sense of it.

This isn't really my job but has been assigned to me as i'm considered the computer geek. I'm good with hardware and windows problems, but haven't done programming beyond 'hello world' before.