Results 1 to 4 of 4

Thread: Macro to Send Excel workbook to specific emails

  1. #1

    Macro to Send Excel workbook to specific emails



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Have you looked at the site's Knowledge base/

    Easy Outlook Integration
    Hope that helps

    Roy

  3. #3
    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!

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Ken's code shows how to add an email address

    Code:
    'Add a recipient
            .AddToRecipient = "To_someone@somedomain.com"
    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.
    Hope that helps

    Roy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •