Outlook Email and VBA (1 Viewer)

galvinjaf

Registered User.
Local time
Yesterday, 20:56
Joined
Jan 5, 2016
Messages
108
Good Morning,

It's been so long since I've tinkered with vba, coding, etc and I can't seem to figure out how to 'edit' the code below. I currently hit a button, and depending on data I have entered, it opens an email using my outlook with the email addresses in the BCC. It works perfect, except that it ONLY works using microsoft outlook. While I see where the application set is Outlook, what can I do to the code to allow any 'default' mail client on different machines to open up? (thunderbird, etc...)

Code:
Private Sub Command76_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDb As DAO.Database
Dim rstEMail As DAO.Recordset
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDb = CurrentDb
Set rstEMail = MyDb.OpenRecordset("Select * From tblEmployee", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![Email] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Below is the link to a new Schedule"
  .Subject = "New Schedule Output"
    .display
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing

End Sub
 

Ranman256

Registered User.
Local time
Yesterday, 20:56
Joined
Apr 9, 2015
Messages
3,639
Instead of CreateObject( OUTLOOK.application,
You create an instance of your eServer.

(Note, outlook is probably in the VB references.)
In the VBE menu, tools, references.
Uncheck Ms Outlook,
Check mark YOUR eServer
 

galvinjaf

Registered User.
Local time
Yesterday, 20:56
Joined
Jan 5, 2016
Messages
108
Thanks for your quick reply. I've checked what you referenced, and can't seem to find in the 'tools' > 'references' menu those particular items. Is there not something I could change in the code?
 

Ranman256

Registered User.
Local time
Yesterday, 20:56
Joined
Apr 9, 2015
Messages
3,639
It's in the vb environment.
Ctl-G (to get in VBE)
Menu,tools,references....
 

galvinjaf

Registered User.
Local time
Yesterday, 20:56
Joined
Jan 5, 2016
Messages
108
I was able to get into the 'references' and I've attached a screen shot of what is checked off. I don't see a 'your server' which maybe I'm not grasping the concept of what you meant by that. Which of the check marks do I uncheck?
 

Attachments

  • Capture.PNG
    Capture.PNG
    18 KB · Views: 36

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:56
Joined
Aug 30, 2003
Messages
34,736
You're using late binding so there is no reference set. I'm not sure what ranman is suggesting, so I'll leave that alone. You can try SendObject which is supposed to use the default email client.
 

galvinjaf

Registered User.
Local time
Yesterday, 20:56
Joined
Jan 5, 2016
Messages
108
Thanks pbaldy, can you tell me what lines I delete, and add in? I understand the concept of what you're saying, but not 100% sure on what to change or edit.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:56
Joined
Aug 30, 2003
Messages
34,736
Basically everything associated with oOutlook and oMail. You would use your variable and strings in the appropriate arguments of SendObject.
 

galvinjaf

Registered User.
Local time
Yesterday, 20:56
Joined
Jan 5, 2016
Messages
108
While I'm not looking for someone to do it for me, I do need a little more context or instruction. I know what I need to get rid of relating to all of the things that reference 'outlook' or 'omail' but once I do that, I'm lost on what to do next.
 

Users who are viewing this thread

Top Bottom