Macro to Send Excel workbook to specific emails

tulliofn

New member
Joined
Jun 5, 2013
Messages
2
Reaction score
0
Points
0
Hi,

I know almost nothing about VBA... all the macros i do, i use the record macro function in excel and i am having trouble creating a macro to send the entire workbook to a specific email, that is located in Cell D1.

The email mus have as subject: "Serviço de atendimento ao Público" and as body "Segue o formulário de atendimento ao público preenchido, qualquer dúvida favor entrar em contato"

I found the following macro in the internet,

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 1 To 1 'data in rows 2-4
' Get the email address
Email = Cells(r, 4)

' Message subject
Subj = "Serviço de Atendimento ao Público"

' Compose the messagef
Msg = Msg & "Segue Formulário de Atendimento ao público preenchido" & vbCrLf
Msg = Msg & "Qualquer dúvida, favor entrar em contato"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg


' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus


' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys "%s"
Next r
End Sub


But this macro does not attach the workbook to the email.... :( I tried solving it by myself, but as said, i know almost nothing on VBA...

Could anyone help me?

Thanks!
 

tulliofn

New member
Joined
Jun 5, 2013
Messages
2
Reaction score
0
Points
0
I have but the codes there do not allow me to change the email address and they require the file to be saved in the computer...

the code below allows me to send the file as an attachment without saving it to the pc, but it does not allow me to write any message or change the recipient address

Sub Envio_Email()
ActiveWorkbook.SendMail "email@email.com.br", "subject"

End Sub

What I need is a code that sends the workbook as an attachment to an email address that is located in cell D1, with a pre defined subject and a email body as well.

If i had at least a begginer level in VBA i think i could manage doing it by combining the codes in the knowledge base to the codes i posted, but i really dont know how to do it and i have to deliver this file by the end of the week!
 

royUK

New member
Joined
Mar 22, 2011
Messages
155
Reaction score
0
Points
0
Location
Derbyshire, UK
Website
www.excel-it.com
Excel Version(s)
most versions
Ken's code shows how to add an email address

Code:
'Add a recipient
        .AddToRecipient = "[EMAIL="To_someone@somedomain.com"]To_someone@somedomain.com[/EMAIL]"

Amend to something like

Code:
'Add a recipient
        .AddToRecipient = Sheet1.Cells(2,4) ' D2 on sheet 1 contains the address.

Study the article then post back with anything you don't understand.
 
Top