Sending Email from Database - Calling Module

[JamesN]

Hi,

I've got a vba code written which adds data entered in an input form to a table. On the back of this I also require an email to be written and opended via outlook to a recipient value in a combo box. The issue i am having is linking the codes together, I've tried calling the other module but it's running and skipping it. Code is below (red is the part I am calling, the message box is working but not the email part of the code)

Private Sub submitdispute_Click()

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from BackOfficeDisputeWarehouse")

rec.AddNew
rec("Company") = Me.company
rec("MonthNo") = Me.month
rec("DateTimeofDispute") = Me.datetime
rec("DateofDispute") = Me.date
rec("ProcessingDate") = Me.ProcessingDate
rec("ReferenceNo") = Me.reference
rec("QA") = Me.QA
rec("SQA") = Me.sqa
rec("Department") = Me.Department
rec("Workstream") = Me.Workstream
rec("SubWorkstream") = Me.SubWorkstream
rec("FLM") = Me.flm
rec("PC") = Me.pc
rec("Agent") = Me.agent
rec("InvestigationDispute") = Me.Inv
rec("InvestigationDisputeComments") = Me.invcomments
rec("CustomerDispute") = Me.Cus
rec("CustomerDisputeComments") = Me.cuscomments
rec("BusinessDispute") = Me.Bus
rec("BusinessDisputeComments") = Me.buscomments
rec("AccountManagementDispute") = Me.Acc
rec("AccountManagementDisputeComments") = Me.acccomments

Call SendEmail

End Sub

Private Sub SendEmail()

Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
oMail.Body = "TEST BODY"
oMail.Subject = "Test Subject"
oMail.To = Me.sqaemail
oMail.CC = Me.qmemail

MsgBox ("Dispute Raised")


End Sub

 

[KenHigg]

1 - I don't see a recordset update (If that is required)
2 - Have you stepped through the code? Sometimes the issue can be realized by doing this...

 

[JamesN]

The update is in the code, i possibly stripped it out when copying it across.

The update of the table is working fine, as for the opening outlook email and populating the details, i'm not getting any errors when running it but its not completing the action.

The codes are working fine seperately but when trying to use the call function and complete as one button click the email doesn't open

 

[jdraw]

Further to Ken's comment, I don't see a Send command in the SendEmail procedure.

This sort of thing

'This sends it!
'MyMail.send


Good luck.

 

[JamesN]

I haven't added the send part of the code in yet as i'm just trying to get it working to open the outlook email and populate the details, however, the current code isn't opening the outlook email?

 

[KenHigg]

Have you tried a DoEvents?
Can you put it all in the same routine?

 

[JamesN]

I didn't put them in the same routine as there are variables in both codes and I wasn't sure if this would cause issues?

I'm not familiar with the DoEvents code, could you share how this code would fit with my code?

Thanks

 

[KenHigg]

As long as the variable names are unique it should be fine to combine them...

You put a DoEvents in after a section or command runs and the system is supposed to let if finish that part before proceeding to the next line. Probably won't help here but its worth a shot...

 

[JamesN]

Tried the below code but still no joy...the record is added to the table but no email is opened

Private Sub submitdispute_Click()

Dim db As Database
Dim rec As Recordset
Dim oApp As Outlook.Application
Dim oMail As MailItem

rec.AddNew
rec("Company") = Me.company
rec("MonthNo") = Me.month
rec("DateTimeofDispute") = Me.datetime
rec("DateofDispute") = Me.date
rec("ProcessingDate") = Me.ProcessingDate
rec("ReferenceNo") = Me.reference
rec("QA") = Me.QA
rec("SQA") = Me.sqa
rec("Department") = Me.Department
rec("Workstream") = Me.Workstream
rec("SubWorkstream") = Me.SubWorkstream
rec("FLM") = Me.flm
rec("PC") = Me.pc
rec("Agent") = Me.agent
rec("InvestigationDispute") = Me.Inv
rec("InvestigationDisputeComments") = Me.invcomments
rec("CustomerDispute") = Me.Cus
rec("CustomerDisputeComments") = Me.cuscomments
rec("BusinessDispute") = Me.Bus
rec("BusinessDisputeComments") = Me.buscomments
rec("AccountManagementDispute") = Me.Acc
rec("AccountManagementDisputeComments") = Me.acccomments

Set oApp = CreateObject("Outlook.application")
Set oMail = oApp.CreateItem(olMailItem)
oMail.Body = "TEST BODY"
oMail.Subject = "Test Subject"
oMail.To = Me.sqaemail
oMail.CC = Me.qmemail

End Sub

 

[KenHigg]

It could be it is opening but you just don't see it. Check if there is a option to view the outlook app object when it opens.

Edit: For Excel it's something like:

ExcelSheet.Application.Visible = True

Not sure of the syntax for Outlook...

 

[JamesN]

You are correct. I added in 'oMail.Display' and that has prompted the email to appear. Thanks for your help

 

[KenHigg]

Cool - Glad you have it working !