VBA to send emails seems to be broken

[dmcfarland9]

Please help. I have been working on this for days and have no solution. My code is not displaying an error but it is also not displaying my populated email. Once I have the VBA correct, then I don't know how to call it via a command button. Pretty new to this coding thing and I learn everything from Google. Please help

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("qry_TermsEmail_")

Do Until rs.EOF

emailto = "insert email addresses here"

emailcc = "insert email CC here"


emailsubject = "insert subject here"

emailtext = emailtext & "Hi Team," & vbCrLf
emailtext = emailtext & " " & vbCrLf
emailtext = emailtext & (rs.Fields("Name").Value & " " & "will be terming from the" & " " & rs.Fields("Team").Value & " " & _
"team on" & " " & rs.Fields("TermDate").Value & ".") & vbCrLf
emailtext = emailtext & " " & vbCrLf
emailtext = emailtext & "After the end of the workday, please remove appropriate accesses." & vbCrLf
emailtext = emailtext & " " & vbCrLf
emailtext = emailtext & "Please initiate applicable off-boarding administrative tasks:" & vbCrLf
emailtext = emailtext & " *Stacy - IDB and Staffing" & vbCrLf
emailtext = emailtext & " *Gabby/Training Coordinator - Workday Partner Portal" & vbCrLf
emailtext = emailtext & " *Danielle - End FTE in IDB, update term spreadsheet" & vbCrLf
emailtext = emailtext & " *Tonya/Alexandra - SharePoint, Remove Internal Resume, and Email distros" & vbCrLf
emailtext = emailtext & " *William/Andy - Remove from POV, if applicable" & vbCrLf
emailtext = emailtext & " *Alex - please remove from HCM Slack group" & vbCrLf
emailtext = emailtext & " " & vbCrLf
emailtext = emailtext & "Team Manager:" & vbCrLf
emailtext = emailtext & " *Please initiate Term in Workday (**see TIP below if you are not aware of the existing Checklist!)" & vbCrLf
emailtext = emailtext & " *Alight Standard Equipment if Alight provided, specifically:" & vbCrLf
emailtext = emailtext & " *Laptop" & vbCrLf
emailtext = emailtext & " *Mouse" & vbCrLf
emailtext = emailtext & " *2 AC adapters" & vbCrLf
emailtext = emailtext & " *Badges" & vbCrLf
emailtext = emailtext & " *Enter of their LAST TIMESHEET to capture remaining billable hours!" & vbCrLf
emailtext = emailtext & " *Ensure colleagues are removed from any team skype chats" & vbCrLf
emailtext = emailtext & " *Ensure the PM/IDM of any current projects they support are informed so they can be removed from any Customer tenant access and communications" & vbCrLf
emailtext = emailtext & " *Work with Resource Manager (Stacy/Practice Leads) for backfilling current work for Functional resources" & vbCrLf
emailtext = emailtext & " " & vbCrLf
emailtext = emailtext & "Danielle McFarland - Operations Coordinator"



Set outmail = outApp.CreateItem(olMailItem)
outmail.To = emailto
outmail.CC = emailcc
outmail.Subject = emailsubject
outmail.Body = emailtext
outmail.Display

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

 

[June7]

Please edit your post to place code within CODE tags to retain indentation and readability.

You don't show procedure declaration line. Is this a Function or a Sub? Probably just paste all that code into button Click event.

 

[GinaWhipp]

Without more information...

1. Does "qry_TermsEmail_" return any records?
2. Umm...

emailto = "insert email addresses here"
emailcc = "insert email CC here"

...you are not telling the above what fields from the Recordset are to be populated. Because this...

outmail.To = emailto
outmail.CC = emailcc

...can't work without knowing those values.

3. Where is this code? You say it's broken but you don't state where you put it or how you are testing it that makes you think it's broken. Looks like to me it's missing information and that's why it's not *running*.

 

[Cronk]

Danielle


A typical set up would be to have a form with a button called Email

In the properties box for that button, select the OnClick command on the Events tab. Click on the elipses on the right hand side and select Code Builder.


Insert your email code between the Private Sub ... and the End Sub and it will execute when the button is clicked (when the form is in Form View)

 

[nhorton79]

Other people may have other ideas, but also try getting rid of the lines:
On Error Resume Next
On Error Goto 0

This might help identifying what lines are creating issues.


Sent from my iPhone using Tapatalk

 

[Gasman]

When this ever happens to me, I walk through the code line by line, or put a breakpoint in, and use the debugger and F8.

 

[dmcfarland9]

Other people may have other ideas, but also try getting rid of the lines:
On Error Resume Next
On Error Goto 0

This might help identifying what lines are creating issues.


Sent from my iPhone using Tapatalk

This is perfect! Worked like a charm. Thank you so much!!