Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VB to Send File as Attachment

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone know if there is a way to create a macro to send the current workbook as an attachement to predetermined receipients? I try to record a macro so I can view the code BUT mailto: automatically addresses the "to" email field but doesn't pick up the attachement and "file"-"sendto"-"mail as attachement" picks up the attachment but doesn't address the "to" field. I need a macro that does it all automatically with the user just clicking a button..

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Keith,

    This code attaches the workbook containing the code to three recipients:

    Dim RecipVal As Variant

    RecipVal := Array("friend1@aol.com","friend2@hotmail.com","friend3@yahoo.com")

    ThisWorkbook.SendMail Recipients:=RecipVal, _
    Subject:="Email Test"
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WHOO HOO! I haven't tried the code yet but I am glad someone posted. No one responded for so long that I went to the next project..

    i guess there really are people that look at messages past the first page of posts...!

    Thank you , thank you, thank you!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I had to take out the : in

    RecipVal := Array

    and change it to just

    RecipVal = Array

    And it works like a dream...

    .SendMail is one to remember... Thanks

  5. #5
    New Member
    Join Date
    Aug 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VB to Send File as Attachment (for Word documents)

    How do I save a word document before sending it as an attachment using VBA? Here is my current code. All data that was filled into the form is erased after I hit run the macro . Please help!!!

    Private Sub Submit_Click()
    Set Destwb = ActiveDocument
    ' Pop up Macro / Attestation confirm
    If Counter Mod 1 = 0 Then
    If MsgBox("I attest to having answered all the questions and to having filled out the form accurately.", vbYesNo) = vbNo Then End
    End If

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .to = ""
    .CC = ""
    .BCC = ""
    .Subject = "Document"
    .Body =
    .Attachments.Add Destwb.FullName
    .Display 'or use .Send
    End With
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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