Send E-Mail to list of individuals defined in a particular Range

**Rajeshkumar R** · 09-24-2013, 09:02 AM

Hello Friends,

Need your help to accomplish subjected query!

Please open the attached file (normally it saved in a network path); this is a standard template which we are using it for some approval process.

Column L will be updated by some corporate person (based on input from some external sources) who is sitting some other plant.

Daily I need to send an update to a list of people as below & I want this to happen just by press “Send Request” button:-

To Recipient: - VBA has to look the Mail ID from E-Mail List worksheet and the Range is C3:C12.

Cc Recipient: - VBA has to look the Mail ID from E-Mail List worksheet and the Range is D3:D12.

Subject of the E-Mail:- “Serial Number 2,4,6,7,8,9,11,12,13,14,15,16 is Waiting for the Approval” (VBA has to pick the serial number based on code updated in Column N i.e. Zero)

Content of the E-Mail:- Excel File where it is saved, that Network path link should appear

Signature of the E-Mail: - Let this be a default signature which is defined in my outlook.

Immediate suggestion is really appreciated!

Thanks & Regards,
Rajeshkumar R

**lokicl** · 09-24-2013, 11:22 AM

Hi Rajesh,

Please find the full working code of your project.

Open your file and saveas .xlsm format. Insert a module and paste the code. Also update the references - Add Microsoft Outlook items and the signature location in the code below at 'Change this string - according to your system setup - c: or D:

_______________________________________________________

Public to_list As String
Public cc_list As String

Sub to_cc_list()

For i = 3 To wksEList.Range("C1000").End(xlUp).Row
    If wksEList.Range("C" & i) <> "" Then
        to_list = to_list & wksEList.Range("C" & i) & "; "
    End If
Next
For i = 2 To wksEList.Range("d1000").End(xlUp).Row
    If wksEList.Range("d" & i) <> "" Then
        cc_list = cc_list & wksEList.Range("d" & i) & "; "
    End If
Next
End Sub

Sub send_mail()
        Dim olApp As Object ' Outlook.Application
        Dim Msg As Object ' Outlook.MailItem
        to_cc_list
        
        
        
        Set olApp = GetOutlookApp
        If olApp Is Nothing Then GoTo ProgramExit
     
        'Change this string - according to your system setup - c: or D:
        SigString = "D:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\test.txt"
        Signature = GetBoiler(SigString)
        
        
        For i = 6 To wksRawdata.UsedRange.Rows.Count
            If wksRawdata.Range("n" & i) = 0 Then
                subject = "Serial Number " & wksRawdata.Range("a" & i) & " is Waiting for the Approval"
                Content = ThisWorkbook.FullName & Chr(10) & Signature
                   Set Msg = olApp.CreateItem(olMailItem)
                   With Msg
                       .To = to_list
                       .CC = cc_list
                       .Body = Content
                       .subject = subject
                       .display
                       .send
                   End With
            End If
        Next
                
ProgramExit:
      Exit Sub
ErrorHandler:
      MsgBox Err.Number & " - " & Err.Description
      Resume ProgramExit
End Sub

Function GetOutlookApp() As Object
  On Error Resume Next
  Set GetOutlookApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Set GetOutlookApp = CreateObject("Outlook.Application")
  End If
  On Error GoTo 0
End Function

Function GetBoiler(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
_______________________

_______________________________

Enjoy maadi.

Regards,
Lokesh

Moderator Note:

Pls use code tags around your code next time as per forum rules.

**lokicl** · 09-24-2013, 11:28 AM

Also update the sheets with the sheetname Rawdata and E-Mail list with wksRawdata and wksEList in VBA editor.
Regards,
Loki

**Rajeshkumar R** · 09-25-2013, 07:44 AM

Hello lokicl,

Thank you so much for your immediate support!

I really appreciate the way you break down my query with the code. As I am a beginner with VBA, formatting the code to get rid of the error is little challenging for me. Hence if you can help me in getting the clarification on below listed issue, it would be truly help full for me.

1. As you said, I had copy paste your code into new Module in VBA and renamed (is there any specific reason for wksrawdata & wksElist name) the worksheet as you mentioned.

2. Now, When I am trying to run the Macro I am getting an Error like “Run-time error ‘424’: Object required”

3. In the VBA code we have something called “For i = some number like 3, 2 6”, where as we have not defined anywhere what is this i!

4. Last but not least, as mentioned early I am completely new to this VBA, So for each of your code if you can update me comments like 'Outlook.Application, 'Change this string - according to your system setup - c: or D:. I can have more clarity on your code.

Once again Thank you for your time on this!

Thanks & Regards,
Rajeshkumar R

**lokicl** · 09-25-2013, 10:50 AM

Hi Rajesh,

below are my comments:

1. As you said, I had copy paste your code into new Module in VBA and renamed (is there any specific reason for wksrawdata & wksElist name) the worksheet as you mentioned
- it should be renamed in the respective Sheet properties

2. Now, When I am trying to run the Macro I am getting an Error like “Run-time error ‘424’: Object required”
- you have renamed the worksheet - Requested you the update the sheet name in the Sheet properties - when you open the VBE Editor - click on the sheet and update the name - hence the error

3. In the VBA code we have something called “For i = some number like 3, 2 6”, where as we have not defined anywhere what is this i!
- "for i" is a loop statement - the number 3, 2 etc defines the start of the counter

4. Last but not least, as mentioned early I am completely new to this VBA, So for each of your code if you can update me comments like 'Outlook.Application, 'Change this string - according to your system setup - c: or D:. I can have more clarity on your code.
- this will run only with Outlook Application
- the string is - your signature stored path e.g. as per my system setup it is D: but mostly the user profiles will be in C:

However attaching the working project. Check you signature path and update the sig string. I will work.

Thanks & Regards,
loki

**Rajeshkumar R** · 09-26-2013, 10:48 AM

Hello lokicl,

Once again Thanks for your valuable explanation and attached file as well. Hope now its working fine. However, I found few observations as below, when I started to work on the file.

1. E-Mail is reaching only to Cc: Recipients and it’s not reaching to any of To: Recipients.

2. Subjected of the E-Mail is only picking up first zero of Serial number like “Serial Number 2 is waiting for the Approval”. Instead it has to pick all serial numbers which is defined as zero in Column N.
“Serial Number 2,4,6,7,8,9,11,12,13,14,15,16 is Waiting for the Approval” (As I mentioned in the first post).

3. File Name is just getting pasted as is like text in the body of the E-Mail, but it has to get pasted as Hyperlink path, so that the recipient can just clink of the link and get open the File.

4. I just thought instead of Signature, let us give the message like “It’s an Auto Generated E-Mail” only.

Thanks & Regards
Rajeshkumar R

**Rajeshkumar R** · 09-28-2013, 06:51 AM

Hello lokicl,

Any help and support on my previous post!

Thanks & Regards,
Rajeshkumar R

**lokicl** · 10-01-2013, 12:43 PM

Hi Rajesh,

Please find attached the updated tool.

Request you update the To list with valid mail id, because it is working fine for me.

Regards,
loki

**Rajeshkumar R** · 10-04-2013, 02:56 AM

Hello lokicl,

Amazing, now the file is working exactly the way I need… Simply superb…. Thank you so much for your great support on this….

Thanks & Regards,
Rajeshkumar R

Send E-Mail to list of individuals defined in a particular Range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Re: Send E-Mail to list of individuals defined in a particular Range

Thread Information

Users Browsing this Thread

Similar Threads

Need to Send the E-Mail as per Defined Criteria!

Using VBA to send a range in pdf via e-mail

Send list of E-mail Addresses to Microsoft Outlook

send mail to a list of destinators

[SOLVED] "User-defined type not defined" error when trying to send e-mail

Bookmarks

Bookmarks

Posting Permissions