insert call email module after export file

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
672
Office Version
  1. 365
Platform
  1. Windows
hi
i have an export file module
i am trying to add a call email as part of the export
i am doing something incorrectly
Rich (BB code):
Sub ExportStBill()
    
    Call WSUnProtect(Worksheets("State Billing"))
    Dim wb As New Workbook
    Dim SaveFolder As String: SaveFolder = "my folder\"
    Dim FileName As String
       
    With Worksheets("State Billing")
        FileName = .Cells(2, "B") & " " & Format(.Cells(1, "B"), "m-yyyy")
        Set tbl = .ListObjects(1)
        tbl.Range.Copy
    End With
   
    Set wb = Workbooks.Add
    wb.Activate
    wb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
    Selection.Columns.AutoFit
   
    'Save and Close New WB
    wb.SaveAs SaveFolder & "\" & FileName, xlOpenXMLWorkbook
    wb.Close SaveChanges:=False
   
    'Back to Main WB
    ThisWorkbook.Activate
    Call WSProtect(Worksheets("State Billing"))
   
    'Inform User
    MsgBox "File Export Complete", vbOKCancel
   
'    If Result = vbOK Then
'
'        Call email1
'    End If
  
End Sub

then this is my email module:
Rich (BB code):
Sub email1()
  Dim sMail As String, sSubj As String, sBody As String

        sMail = "mail@mail.org"
        sSubj = "Billing ready for submission"
        sBody = "Billing is ready for submission in myfolder"
        Call SendMail(sMail, sSubj, sBody)
     
    End Sub

Sub SendMail(sMail, sSubj, sBody)
  Dim OutlookApp As Object
  Set OutlookApp = CreateObject("Outlook.Application").CreateItem(0)
  With OutlookApp
    .To = sMail
    .Subject = sSubj
    .Body = sBody
    .Display 'Display Email
    .Send 'Send Email
  End With
End Sub

potentailly i would like to call email1 and then create also an email2

this is not working
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Remove
VBA Code:
.Display
and leave
VBA Code:
.Send
in email part. (of course if you want to send message in background but I believe that exactly what you want).

In you main code part which decides about sending email (If Result = vbOK Then ...) is commented by ', so is ignored by vba.
Remove ' and should be ok.
 
Upvote 0
i incommented it doesnt work
it exports but doesnt send the email
 
Upvote 0
Are you sure that Result is OK? I do not see that you assign anything to it.
 
Upvote 0
doesnt this do that:
'Inform User
MsgBox "File Export Complete", vbOKCancel
 
Upvote 0
put
VBA Code:
debug.print Result
below line with MsgBox and you will see.

p.s.
Code:
Result = MsgBox("File Export Complete", vbOKCancel, "result")
 
Last edited:
Upvote 0
i am stuck somewhere
the debug gives me the date of file save and not ok cancel
 
Upvote 0
do i need to make either of the subs public or global?
 
Upvote 0
No.
Try:
VBA Code:
Result = MsgBox("File Export Complete", vbOKCancel, "Message")
If Result = vbOK Then
     Call email1
End If
 
Upvote 0
thanks
that worked
do i do the same when i add email2 to another export module?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top