Email automatisation including data from an excel file

Hello good Afternoon,

I am trying to automate data collection by including some scripts in my excel spreadsheet. The idea is that every time anybody uses the excel sheet I will receive the data on my email account for verification purposes. Since the excel knowledge of my peers is very limited, I automated everything: they just need to click on a button, select the data file and they automatically will have access to the graphs that they require. At this point, everything runs ok on my script and I'm receiving the emails but I'm really struggling to find a way to include the data as an attachment. On top of that, it is working with xcls files, but I still cannot import any csv data.

I found several tutorials working with Outlook, but I would prefer to work with Gmail and everything I tried was unfruitful.

The script that I have right now is:

Sub Get_Data_From_File()
Dim filetoopen As Variant
Dim openbook As Workbook
Application.ScreenUpdating = False
filetoopen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xlsx*),*csv*")
If filetoopen <> False Then
Set openbook = Application.Workbooks.Open(filetoopen)
openbook.Sheets(1).Range("A1:P30000").Copy
ThisWorkbook.Worksheets("Raw Data").Range("A1").PasteSpecial xlPasteValues
openbook.Close False

End If
Application.ScreenUpdating = True


Dim NewMail As CDO.Message
Dim mailConfiguration As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String



Set NewMail = New CDO.Message
Set mailConfiguration = New CDO.Configuration
Set fields = mailConfiguration.fields

With NewMail
.Subject = "data file"
.From = "XXXXXXXXX"
.To = "XXXXXXXXXX"
.Subject = "Test"
.TextBody = "en pruebas"


End With



msConfigURL = "xxxxxxx"

With fields
.Item(msConfigURL & "/smtpusessl") = True
.Item(msConfigURL & "/smtpauthenticate") = 1

.Item(msConfigURL & "/smtpserver") = "XXXXXX"
.Item(msConfigURL & "/smtpserverport") = 465
.Item(msConfigURL & "/sendusing") = 2
.Item(msConfigURL & "/sendusername") = "XXXXXXX"

.Item(msConfigURL & "/sendpassword") = "XXXXXXXX"

.Update

End With

NewMail.Configuration = mailConfiguration
NewMail.Send

MsgBox "Data successfully processed", vbInformation



End Sub


Please I would appreciate if you can help me to include the relevant script, so the data that is copied at the beginning of the macro is also attached to the automated email. I reckon that my knowledge is extremely limited, in deed, this is the first time I work with macros or VBA.


Thank you very much in advance for your help,

Best regards

Fernando

Did some searching on Google...

Possibly...

Please Login or Register  to view this content.