insert call email module after export file

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
i incommented it doesnt work
it exports but doesnt send the email
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Are you sure that Result is OK? I do not see that you assign anything to it.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

doesnt this do that:
'Inform User
MsgBox "File Export Complete", vbOKCancel
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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:

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

i am stuck somewhere
the debug gives me the date of file save and not ok cancel
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
do i need to make either of the subs public or global?
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
No.
Try:
VBA Code:
Result = MsgBox("File Export Complete", vbOKCancel, "Message")
If Result = vbOK Then
     Call email1
End If
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
thanks
that worked
do i do the same when i add email2 to another export module?
 

Forum statistics

Threads
1,148,364
Messages
5,746,275
Members
424,003
Latest member
paaskanama

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
Top