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
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
That should works in general.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
thank you so much
please explain what your message "message" means
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
That's title for msgbox window.

MsgBox( prompt [, buttons ] [, title ])

  • prompt – This is a required argument. It displays the message that you see in the MsgBox. In our example, the text “This is a sample MsgBox” is the ‘prompt’. You can use up to 1024 characters in the prompt, and can also use it to display the values of variables. In case you want to show a prompt that has multiple lines, you can do that as well (more on this later in this tutorial).
  • [buttons] – It determines what buttons and icons are displayed in the MsgBox. For example, if I use vbOkOnly, it will show only the OK button, and if I use vbOKCancel, it will show both the OK and Cancel buttons. I will cover different kinds of buttons later in this tutorial.
  • [title] – Here you can specify what caption you want in the message dialog box. This is displayed in the title bar of the MsgBox. If you don’t specify anything, it will show the name of the application.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
now it gives me the export complete message twice
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

put your code here after changes (only ExportStBill part)
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
thanks
icommented out my message box and only kept yours
this is superb
 

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
429
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I suppose that you have MsgBox twice now :)
Happy to help.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
460
Office Version
  1. 365
Platform
  1. Windows
stay safe
bless you for all
 

Forum statistics

Threads
1,148,367
Messages
5,746,283
Members
424,006
Latest member
Metal_warrior

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