+ Reply to Thread
Results 1 to 4 of 4

macro to send mail

Hybrid View

dorabajji macro to send mail 12-07-2019, 03:17 PM
TMS Re: macro to send mail 12-07-2019, 04:41 PM
k1dr0ck Re: macro to send mail 12-09-2019, 01:39 AM
LJMetzger Re: macro to send mail 12-10-2019, 01:09 PM
  1. #1
    Forum Contributor
    Join Date
    07-10-2019
    Location
    england
    MS-Off Ver
    2013
    Posts
    486

    macro to send mail

    Hi,
    I want to send mail with the active excel file as attachment..
    Eg:cell A1- the mail address is inputted..
    Whenever the user clicks it should send..
    Through macro

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    49,655

    Re: macro to send mail

    See: https://www.rondebruin.nl/win/s1/outlook/amail1.htm
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor
    Join Date
    12-01-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2021
    Posts
    983

    Re: macro to send mail

    try

    from sintek send email using gmail
    modified to attached current excel file

    set reference to Microsoft CDO for Windows2000 library
    needs 2-step authentication disabled and less secure apps enabled in the gmail settings


    Option Explicit
    
    Sub Send_Email()
    
    Dim NewMail As CDO.Message
    Set NewMail = New CDO.Message
    ' ! To get these details you can get on Settings Page of your Gmail Account...................
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
     NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "whateveryourgmailemailis"
    NewMail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"
    NewMail.Configuration.Fields.Update
    
    With NewMail
        .To = Range("A1").Value
        .From = "whatever@gmail.com" '---your email address
        .Subject = "Whatever you like"
        .HTMLBody = "Dear Whoever " & vbNewLine & vbNewLine & "Herewith an email with file attached."
        .AddAttachment ThisWorkbook.Path & "\" & ThisWorkbook.FullName & ".xlsx"
        .send
    End With
    Set NewMail = Nothing
    End Sub
    Last edited by k1dr0ck; 12-09-2019 at 05:01 AM.

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: macro to send mail

    @k1dr0ck thank you for sharing your code.

    When using library references 'Early Binding' is used:
    Dim NewMail As CDO.Message
    Set NewMail = New CDO.Message
    The same thing can be accomplished without using library references using 'Late Binding:'.
    Dim NewMail As Object
    Set NewMail = CreateObject("CDO.Message")
    Reference: http://peltiertech.com/Excel/EarlyLateBinding.html

    I prefer 'Late Binding' because Library References are NOT required. Microsoft and many pros prefer 'Early Binding'.

    Pros of Early Binding:
    a. Code generally runs slightly faster
    b. You can take advantage of VBA 'Intellisense' during development
    c. Constants from the Library Reference are available

    Pros of Late Binding:
    a. Library references not required
    b. You don't have to worry about having the wrong version of the Library Reference, especially when a future version of Excel becomes current.

    Cons of Late Binding:
    a. Intellisense not available for Library Items
    b. Library Constants are NOT AVAILABLE and must be explicitly defined (e.g. Outlook constant: 'Public Const olMail = 43')

    Lewis

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Send PDF to E-mail with Macro
    By callum.mcgrath in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2019, 05:51 AM
  2. [SOLVED] Need to send a mail using macro
    By Prabakaran3003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-10-2016, 03:04 PM
  3. Replies: 1
    Last Post: 03-11-2014, 12:24 PM
  4. How to e-mail selected row and use e-mail address in a cell to send e-mail from excel
    By syedalamgir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2010, 02:15 AM
  5. Send Mail Macro
    By jregan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2010, 07:34 PM
  6. Send Mail Macro
    By jregan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-09-2010, 03:50 PM
  7. using macro to send e-mail
    By bob777 in forum Excel General
    Replies: 1
    Last Post: 11-02-2005, 07:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1