Updating Excel files, converting to pdf and automated sending via email

[BravoSierraTango]

Hello everybody,
first of all I would like to thank you in advance for your help.

I am trying to minimize my workload a bit. At the moment I am managing 250 reports each month additionally to my other work and I decided that a script could help me great times with that.

The cript I have made so far is quite easy I think:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("ORIGINFOLDER_OF_EXCELFILES")
Set arrFiles = objFolder.Files
For Each strFolderName In arrFiles
	Set objExcel = CreateObject("Excel.Application") 
	Set objWorkbook = objExcel.Workbooks.Open(strFolderName)
	objExcel.ActiveWorkBook.refreshall
	for each objworksheet in Array(objworkbook.Worksheets("SVR"))
		objWorksheet.Cells(2, 13) = "Jan-"
		objWorksheet.Cells(2, 14) = "Jun"
		objWorksheet.Cells(2, 15) = "2016"
	next
	objExcel.Run "Save_as_pdf"
	objExcel.ActiveWorkbook.Save()
	objExcel.ActiveWorkbook.Close
Next
objFSO.MoveFile "ORIGINFOLDER_OF_EXCEL*.pdf", "DESTINATIONFOLDER_OF_PDF"

WScript.Echo "Finished."
WScript.Quit

What I did not find out as of now is how I can tell the script to ONLY publish the first sheet. Because on the others there are only calculations and other information that might confuse them.

Furthermore I am not sure how to tell the system to send it via Email. That's what I've got so far:

Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "Reports 062016" 
            .To = "name" 
            .Body = "Dear..., please find enclosed....." & Chr(13) & Chr(13) & "Kind regards," & Chr(13) & "My name& Chr(13) & "departmentcode"
            .Attachments.Add tempPDFFileName
            .Send
    End With
        MsgBox "", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

As I am not even remotely working in this field of work usually, I am quite thrown with this and did not find out how to merge both commands.
I only had HTML and C++ on a VERY low niveau in school and now I am desperately trying to learn vba since a week *shame on me and my teachers*
So please be kind and ignore the (supposedly) infanitlity of my work. I am really trying hard here.

P.S.: If anybody can suggest a good way to learn vba quicker, please let me know. I hate to do things half-assed and would like to improve!

Kind regards and I wish you all the best!
BravoSierraTango

 

[yky]

Maybe the following link will help:

https://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx

 

[BravoSierraTango]

Thank you for the link! I will take a look into it. On the first glance I already saw some quite usefull partes for me. Maybe I can find a solution for this problem.