E-mail Reports from VBA or Macro

[maw230]

I would like to be able to auto (or at the push of a button) e-mail my reports using VBA or a macro.
We use Lotus Notes here at work, but it is pretty locked down, so I'm thinking my only option is to get with PC support if I were to go this route.

I was unable to connect my g-mail account to my work PC's Outlook 2007 via POP3, and I'm guessing that is also locked down by the sysadmin.

So when I chose "Email" after pressing the Office Button in Access I get an error. Which is of course because I don't have an account setup in Outlook.

I found this code online:

Private Sub LotusMail(Recipient As Variant, cc As Variant, Bcc As Variant, Subject As String, BodyText As String, Attachment As Variant, SaveIt As Boolean)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text.
 
'Requires that notes client is installed on the system.
Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object
 
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim MsgReport As String
 
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
 
'Get the sessions username and then calculate the mail file name.
'You may or may not need this as for MailDBname with some systems you can pass an empty string
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
 
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
 
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.CopyTo = cc
MailDoc.BlindCopyTo = Bcc
MailDoc.Subject = Subject
 
'Set up the embedded object and attachment and attach it
Set AttachME = MailDoc.CreateRichTextItem("Attachment")
    For i = 0 To UBound(Attachment)
        If Attachment(i) <> "" Then
            Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment(i), "Attachment")
        End If
    Next i
'MailDoc.CreateRichTextItem ("Attachment")
 
MailDoc.Body = BodyText
MailDoc.SaveMessageOnSend = SaveIt
 
 
 
'Send the document
MailDoc.Send 0, Recipient
 
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
 
MsgReport = "Message has been Send to:"
    For i = 0 To UBound(Recipient)
        If Recipient(i) <> "" Then
            MsgReport = MsgReport & vbCrLf & Recipient(i)
            Else: GoTo LineCC
        End If
    Next i
      
LineCC:
If cc(0) <> "" Then
    MsgReport = MsgReport & vbCrLf & "Cc:"
    For i = 0 To UBound(cc)
        If cc(i) <> "" Then
            MsgReport = MsgReport & vbCrLf & cc(i)
            Else: GoTo LineBCC
        End If
    Next i
End If
 
LineBCC:
If Bcc(0) <> "" Then
    MsgReport = MsgReport & vbCrLf & "Bcc:"
    For i = 0 To UBound(Bcc)
        If Bcc(i) <> "" Then
            MsgReport = MsgReport & vbCrLf & Bcc(i)
            Else: GoTo LineMSG
        End If
    Next i
End If
 
LineMSG:
 
MsgBox MsgReport, vbInformation, "Louts Notes report"
 
End Sub

But I am unsure how to use VBA in Access. I created a new module, pasted the code in, but then could not locate the Macro in the drop down box after clicking "Run sub/user form".

Please any suggestions on how to do this. I am just now remembering the search forum feature...

 

[brodkat]

I am certainly not the most qualified to answer your question but no one else is biting so I have an idea for you. In the crudest of explanations, I think your code needs 'a way' to run. You need to insert an 'event' in your form that runs the code. Running it directly from the VB editor isn't as easy as just clicking the run button (I'm a beginner too so I could be wrong about that but I can only get things to run from the form).

So you said you wanted a button for the user to click to run your email program. A button is a good way to get your code to run too so to do that:
1. open your form in design mode
2. add a button (forms tools/design > controls > button)
3. escape out of button wizard.
4. open the property sheet
5. go to the other tab and name your button with Name property
6. Choose event tab, go to 'on click' row and using the drop down arrow select [Event Procedure].
7. Click the ellipses (...) in the On Click Event box.
That will spawn the VB editor and a new button subroutine, which is where your code is inserted.

really, I am struggling teaching myself and am a super beginner - I didn't evaluate your actual code but I do know that trick for launching VB code. Hope it helps even a little bit.

 

[stopher]

The code basically creates a subroutine that can called when needed. However, it's a Private subroutine so if you stick it in a module then you won't be able to call it elsewhere (because it's private!).

Either change the word private to public so your function can be called anywhere.

Or, put the code in below the button code described by brodkat. So your button code will look something like:

Private Sub MyButton_Click()

'some code for your button include a call to the LotusMail subroutine
.....
....

End Sub


Private Sub LotusMail(Recipient As Variant, cc As Variant, Bcc As Variant, Subject As String, BodyText As String, Attachment As Variant, SaveIt As Boolean)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text.
...
...

End Sub

So all that's needed is the code to put in your button Sub to call the LotusMail sub. A simple subroutine (one recipient, one attachment etc) might take a string argument and be called like this:

Call LotusMail("someone@somewhere.com","somecc@somewhere.com"..Etc)

However, the code you have allows for multiple recipients etc and therefore requires some of the arguments to be arrays e.g. the recipient needs to be an array of recipients.

So you need to either simplify the LotusMail subroutine if you just want to send an email to one recipient etc, or write some code to populate your arrays. If you populate the arrays then you can call LotusMail like this example:

Call LotusMail(arrRecipient, arrCC, arrBCC, "my subject", "my body text", arrAttachment, true)

To get started with VBA you might want to try some tutorials. Here's some I found:
http://www.learnaccessvba.com/free_tutorials/free_tutorials_with_video.htm

http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/vb_intr.pdf

http://www.vtc.com/products/Microsoft-Visual-Basic-for-Access-tutorials.htm

If you get stuck, post your code and ask more questions.

hth
Chris