Create PDF>file save as cell Contents>Email

[afajob]

Good Evening ladies and gents.

I have searched long and hard but am stumped when it comes to how to complete my project.

Many hours have been sent scouring the forum, I guess I just get really confused when it comes to joining the codes together and I feel what i need is very specific.

Below is every step I have done up to now and with the right eyes checking this i bet its actually quite simple to get going.



I would be deeply appreciate if anyone could take a look at it and thank those in advance for your help.



https://skydrive.live.com/redir?resid=35A4FF48588D1F3F!226&authkey=!ANd_EQPIq6RTSZU



1. The user inputs the details in the boxes below.
2. User Presses the button on the right (VBA)
3. Button is linked to several macros -

A. Changes the red number (invoice number) in sequential order using this code

Public Sub pub()[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]Sheets("Sheet1").Range("L5").Value = _[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]Sheets("Sheet1").Range("L5").Value + 1[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]End Sub[/COLOR][COLOR=#ff0000][code\][/COLOR]


B. Saves the workbook as a PDF in desired location whilst using cell B62 [COLOR=#FF0000]="#"&L5&" Broken Rail Report "[/COLOR]      for name and sequence number sourced from L5
using this code


[COLOR=#FF0000][code][/COLOR][COLOR=#FF0000]Public Sub macro()[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]ActiveSheet.ExportAsFixedFormat  Type:=xlTypePDF, Filename:="X:\Loco demand\Train Control Online  Library\Broken Rail Report\trial\" & Range("B62").Value &  Format(Date, "ddmmmyy")[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]
[/COLOR][COLOR=#FF0000]End Sub[/COLOR][COLOR=#FF0000][code\][/COLOR]
For  this code to work I would have to change the above save  location to something relevant to myself or create and map the drive  for testing purposes or change the path[COLOR=#000000]which is fine.[/COLOR][COLOR=#FF0000]
[/COLOR]
[COLOR=#FF0000][code][/COLOR][COLOR=#FF0000][COLOR=#FF0000]Public Sub macro()[/COLOR][COLOR=#FF0000]
[/COLOR]
[/COLOR][COLOR=#FF0000]ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="[/COLOR][COLOR=#FF0000]C:\my document\work\new folder\" & Range("B62").Value & Format(Date, "ddmmmyy")[/COLOR]

[COLOR=#FF0000]End Sub[/COLOR][COLOR=#FF0000][code\][/COLOR]

The end of the last code adds the date

File example would be = [COLOR=#FF0000]#5 Broken Rail Report 09JAN13.pdf[/COLOR]


C.  I recorded a macro within excel going through each cell that is free  for writing in and individually pressed the delete button this in turn  clears all the information
from the workbook (except the sequence  number) for the next user. I also went to File>save to record the  saving of the original document as a Xlsm not pdf.

And have now pressed stop record.

D.  This macro is again another recording this time going through and  starting all the macros in order as to make it appear that this is all  happening at once.

And have now pressed stop which end any more writing to the save macro function.




What i need is where the code saves the .pdf in step B to then also 

1. open outlook 
2. insert the PDF file
3. insert list of email into TO: section (getting emails from E.g cell B70
4. insert subject
5.  Press send button (when opening an email in outlook from external  program will automatically close once the send button is pressed)


Then carry on with the rest of above steps.

all  the user does then is press the close button in the corner or from the  file menu (because we have already saved the template excel will not ask  to save changes).