I have an excel workbook that I must email out to my team on a daily basis. The excel workbook needs to be refreshed daily from two text files that are downloaded from our wms system. The text files are imported to the excel workbook and the workbook is emailed to the team. I am trying to automate this so I do not have to open the excel workbook, click data, refresh all, choose the files to refresh, click the office button, click send, as email attachment, type in the email address and click send.

I did come across the following VB code that is very helpful to me although I have one concern. The concern is that when this workbook is emailed out to the team and they open it, I think the macro will run and import the data and email the workbook again. I can see some people opening the emails continuously and creating a repetitive loop that can be very frustrating to others on the email chain.

Is there a way to have the VB code do everything but only on my side and not run the VB macro when the email is sent?

Sub Auto_Open()
'
' Auto_Open Macro
' refresh data and send email
'

'
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
'

' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "enter email address"
.CC = ""
.BCC = ""
.Subject = "Daily Report"
.Body = "Attached is the daily report."
.Attachments.Add ActiveWorkbook.FullName
' You can add other files by uncommenting the following line.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

ActiveWorkbook.Close True

End Sub