Results 1 to 4 of 4

Thread: Attach File To An Email Using VB Code

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Post Attach File To An Email Using VB Code

    Hi,

    I have the code below and I need help to modify it by
    1) adding a line telling the macro to attach a cetain file from my desktop ( for example C:\test.pdf )
    2) I would like to change the charecters inside the mails for some lines to bold

    Can anyone help please. Thank you in advance

    Code:
    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 Notification()
        Dim Email As String, Subj As String
        Dim Msg As String, URL As String
        Dim r As Integer, x As Double
        
        NumOfRows = Sheets("mail template").Cells(Rows.Count, 2).End(xlUp).Row
    For r = 2 To NumOfRows
    
     
            Email = Cells(r, 3)
    
            Subj = "Assigned to You"
    
            Msg = "" & vbCrLf & vbCrLf
            Msg = Msg & "Dear XY," & vbCrLf & vbCrLf
            
            Msg = Msg & "Your are requested to contact you department's manager " & Cells(r, 15) & "  for review." & vbCrLf & vbCrLf
            
            Msg = Msg & "Please contact the interviewer promptly" & vbCrLf & vbCrLf
            
            Msg = Msg & "Candidate:       " & Cells(r, 3) & vbCrLf
            Msg = Msg & "Capability:      " & "forums" & vbCrLf
       
           
            Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
            Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                    
            Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
    
             URL = "mailto:" & Email & "?cc=mail@mail.com" & "?subject=" & Subj & "&body=" & Msg
    
            ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
    
            Application.Wait (Now + TimeValue("0:00:02"))
            Application.SendKeys "%s"
        Next r
    End Sub

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    majdi.marmas, welcome to the ExcelFox community.

    Why don't you use one of the more conventional methods of sending a mail, using VBA. You can find a lot of links in this forum (and on the internet generally)

    Some examples are

    http://www.excelfox.com/forum/f2/sen...ach-files-498/

    http://www.excelfox.com/forum/f2/exc...end-email-556/
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Thank you very much for your replay, I have find some solution to add to my existing code. I'd like to share it in case someone would like to use it.

    Code:
     URL = "mailto:" & Email & "?Attach=C:\test1.pdf?CC=" & cpy & "?subject=" & Subj & "&body=" & Msg

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Note that these are for sending email via Outlook. If you are using Lotus Notes, or if you are not able to figure out the codes in the threads suggested above, please post back.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 2
    Last Post: 08-23-2013, 01:11 PM
  2. Replies: 1
    Last Post: 07-26-2013, 01:54 AM
  3. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  4. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  5. Replies: 1
    Last Post: 05-22-2013, 01:50 PM

Posting Permissions

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