Results 1 to 9 of 9
  1. #1
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181

    Send email using VBA and outlook

    I found some code to send multiple emails via outlook using my default account and it works fine. What I would like to do is send the emails using a specific account. Our IT department has kindly set up for me an email account for testing which I have added to my account list in outlook. I have found code for "send using account" but unsure how to code this. All help is appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What method are you using - Outlook automation, not SendObject method?

    Post your code for analysis. If you have code for 'send using account' exactly what about it is not clear?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Hi June 7

    The code i am using is below, courtesy of http://codekabinett.com, which i have tweaked to suit what. on one of my forms i select the email addresses i want to send the email to and send. The email(s) are sent through my default account in Outlook which is my personal account. I have another account in my outlook mail list which is for business use and i would like to change the code so that the mail recipient sees the "from" as my business email and not my personal email address. One other thing i need to do is add a signature to this email which i am unsure to do in VBA. I hope this makes sense.

    Public Sub SendSerialEmail()


    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String


    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean


    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
    Set outApp = CreateObject("Outlook.Application")
    outlookStarted = True
    End If


    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT FirstName, Surname, EmailAddress, DeskVacated, VacateDesk " & _
    " FROM qryPGRDesks1FilterAll")
    Do Until rs.EOF


    emailTo = Trim(rs.Fields("FirstName").Value & " " & rs.Fields("Surname").Value) & _
    " <" & rs.Fields("EmailAddress").Value & ">"

    emailSubject = "Notification to vacate desk"
    If IsNull(rs.Fields("FirstName").Value) Then
    emailSubject = emailSubject & " for " & _
    rs.Fields("FirstName").Value & " " & rs.Fields("Surname").Value
    End If

    emailText = Trim("Hi " & rs.Fields("FirstName").Value) & vbCrLf & vbCrLf
    'If rs.Fields("DeskVacated").Value Then
    'emailText = emailText & "Here is a special offer only for VIP customers!" & _
    '"Only this month: Get the foo widget half price!" & vbCrLf
    'End If

    emailText = emailText & _
    "This email is to inform you that your desk needs to be vacated on " & rs.Fields("VacateDesk").Value & "." & " " & _
    "Please contact the Faculty Infrastucture Team should you need to discuss this request." & " " & _
    "Our email address is hinchi1@gmail.com " & vbCrLf & vbCrLf & _
    "Best Regards " & vbCrLf & vbCrLf & _
    "Faculty Infrastructure Team - Desk Allocation" & vbCrLf & _
    "Extension Number: 00000 "



    Set outMail = outApp.CreateItem(olMailItem)
    outMail.To = emailTo
    outMail.Subject = emailSubject
    outMail.Body = emailText
    outMail.Send


    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If outlookStarted Then
    outApp.Quit
    End If

    Set outMail = Nothing
    Set outApp = Nothing

    End Sub

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You said you have code for 'send using' but not seeing that in your example.
    Here is an example of send using https://www.pcreview.co.uk/threads/v...count.3997983/

    Pulling a particular saved signature file seems rather complicated. So far I can't get the default signature for my account to pull into the email. Could construct the email with HTML formatting and the signature would just be more formatted text or embed an image.


    Please post code between CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    sorry, here is the first part, ignore the commented out parts. This part of the code counts how many records i have to email and at his point i am prompted to send or not. if i Vbyes then the previous code kicks in and loops the the emails i have selected to send and sends via Outlook. The link you sent me is on the right lines but i dont want have to select an email account to send it from, i just want it to send by the specific email address in the code, i that makes sense.

    Private Sub cmdCurrentMonth_Click()
    'On Load of the switchboard check Jobs table for any uncompleted jobs


    Dim intStore As Integer


    'Count of uncomplete jobs that are past the Expected Completion Date
    'intStore = DCount("[ID]", "[qryPGRDesks1Filter]", "[VacateDesk] <=Date() or Date()-1 AND [DeskVacated] =0")
    intStore = DCount("[ID]", "[qryPGRDesks1FilterCurrentMonth]", "[VacateDesk]")
    'AND [DeskVacated] =0")






    'If count of uncomplete jobs is zero display switchboard
    'Else display message box detailing amount of jobs
    'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
    Exit Sub
    Else
    If MsgBox("You have " & intStore & " email(s) to send" & _
    vbCrLf & vbCrLf & "Would you like to send now?", _
    vbYesNo, "Send Emails...") = vbYes Then
    'DoCmd.Minimize
    'DoCmd.OpenForm "frmPGRDesks1Filter", acFormDS, "", "", acEdit, acNormal
    'DoCmd.OpenForm "frmPGRDesks1Filter", acNormal, "", "", acEdit, acDialog
    'DoCmd.OpenForm "frmReminders", acNormal, "", "[ID]=" & ID, acEdit, acNormal
    SendSerialEmailCurrentMonth
    Else
    Exit Sub
    End If
    End If
    End Sub

  6. #6
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Just had another look at the link i will try this when i am back at work on monday, thanks again.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Finally found a method to include my default email address. However, don't know if will work for your 'send using' requirement.

    It involves Replace() function as described in answer by Eliot K in https://stackoverflow.com/questions/...ure-in-outlook
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Thanks for this, but I will need to study it for a while. Quite new to coding so this looks quite complicated.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you don't need your message to have any formatting then perhaps this will work for you https://www.accessforums.net/showthread.php?t=68563
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-14-2015, 07:24 AM
  2. Replies: 5
    Last Post: 07-21-2015, 05:27 AM
  3. How to send formatted email without outlook
    By alex1888 in forum Programming
    Replies: 2
    Last Post: 06-04-2015, 08:32 PM
  4. Send email in Outlook with attachment
    By kelkan in forum Programming
    Replies: 1
    Last Post: 02-01-2013, 10:31 PM
  5. Send email from Access thru Outlook
    By ZMAN in forum Forms
    Replies: 2
    Last Post: 11-27-2010, 06:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums