[RESOLVED] [Excel] Auto email workbook code help

[Bamahut]

Hi,

I need a code that lets me send a workbook that's active on my screen without saving it, as the clients will fill it and send it back to me.

I need to be able to type in the addresses that it's being sent to.

I tried the code below but it doesn't send my excel workbook with the new data into it for some reason, it send the older version back.

Private Sub BoutonEnvoiEmail_Click()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .To = TextBoxEnvoiEmail.Text
        .Subject = "Bon de Travaux" & Format(Date, "dd/mmm/yy") & Format(Time, "hh:mm")
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
ActiveWorkbook.Close SaveChanges:=False

End Sub

[Bamahut]

I forgot to add that I use another code for users to send the workbook back to me.

Private Sub BoutonEmail_Click()
    Application.Dialogs(xlDialogSendMail).Show arg1:="[email protected]", _
                      arg2:="Bon de Travaux"
End Sub

Is it possible to make it to auto send without an excel window to open?

[westconn1]

I tried the code below but it doesn't send my excel workbook with the new data into it for some reason, it send the older version back.

it will save the last saved version, save first, or, if you do not want to keep the changes, then use savecopyas, change attachment name to the name of the saved copy, then delete after sending

[Bamahut]

Alright, so I remember that I could use an input-box that would let me manually input the email address:

    Application.Dialogs(xlDialogSendMail).Show arg1:=InputBox(Prompt:="Address Email", Title:="Insert Email", Default:="@croix-rouge.fr"), _
                      arg2:="Bon de Travaux" & " " & Format(Date, "dd/mmm/yy") & " " & Format(Time, "hh:mm")

The other problem I have now is with the auto copy code that I use on another button.

strFirstFile = "Schéma récap MG.xls" 'Use the opened document as source
strSecondFile = "\\prc2507et001\IRFSSMP_31\IRFSS\_MAINTENANCE\Récup Info.xls" 'Path for destination document

Set wbksour = Workbooks(strFirstFile)

Everytime the workbook is sent, it's sent as "Schéma récap (2)" or 3 and so on, so can I use a code that will specify to use the openned workgroup without the need to change the name every-time?

I found the solution to my question, I just use: ActiveWorkbook()
This lets my code save the data without having to worry about the name of the file.

Dim wbksour As Workbook
Dim wbkdes As Workbook
Dim strSecondFile As String

strSecondFile = "\\prc2507et001\IRFSSMP_31\IRFSS\_MAINTENANCE\Récup Info.xls" 'Path for destination document

Set wbksour = ActiveWorkbook() 'Use the activeworkbook regardless of name
Set wbkdes = Workbooks.Open(strSecondFile)
    nextrow = wbkdes.Sheets("Récup Info").Cells(wbkdes.Sheets("Récup Info").Rows.Count, 1).End(xlUp).Row + 1 'Select next row
        wbkdes.Sheets("Récup Info").Cells(nextrow, 1) = wbksour.Sheets("Bon de Travaux").Cells(28, 16) 'Copies P28 into Cell A2 of wbkdes.Sheets("Récup Info")
        wbkdes.Sheets("Récup Info").Cells(nextrow, 2) = wbksour.Sheets("Bon de Travaux").Cells(6, 6) 'Copies F6 into Cell B2 of wbkdes.Sheets("Récup Info")
        wbkdes.Sheets("Récup Info").Cells(nextrow, 3) = wbksour.Sheets("Bon de Travaux").Cells(8, 6) 'Copies F8 into Cell C2 of wbkdes.Sheets("Récup Info")
        wbkdes.Sheets("Récup Info").Cells(nextrow, 4) = wbksour.Sheets("Bon de Travaux").Cells(10, 6) 'Copies F10 into Cell D2 of wbkdes.Sheets("Récup Info")
        wbkdes.Sheets("Récup Info").Cells(nextrow, 5) = wbksour.Sheets("Bon de Travaux").Cells(12, 6) 'Copies F12 into Cell E2 of wbkdes.Sheets("Récup Info")
        wbkdes.Sheets("Récup Info").Cells(nextrow, 6) = wbksour.Sheets("Bon de Travaux").Cells(14, 6) 'Copies F14 into Cell F2 of wbkdes.Sheets("Récup Info")
    wbkdes.Save 'Save the Destination worksheet
    wbkdes.Close 'Close the Destination worksheet