• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to send each file in folder through outlook with conditional, ID, Subject & Body with Signature?

Dear All Experts & Users,

Good Day & many wishes God Bless you in your fulfilment of dreams

SUBJECT:- How to send each file in folder through outlook with conditional, sending ID, Subject & Body Text with formatted signature?
  1. I have 50 files in one folder.

  2. Each file name start with 5 digit number which is buyer no (customer id)..

  3. I have a outlook group IDs named same as their customer ID which have their multiple ids (for example -in outlook , in “To”, if I just put 11345 (group name) , mail send to that group 11345 have multiple id so mail goes to that multiple ids)
I require

  1. code that read folder’s files’s first 5 digit & search that 5 digit in outlook group if found put That group name in “To” field

  2. Attaché that file in that mail

  3. For subject field also search that 5 digit in Excel buyer master file that reside in particular fixed location & lookup name for that 5 digit buyer code (Example -excel file column A have buyer no like 11345 Column B have name of that buyer code like “Senator”) & put this sentence in subject “11345-SENATOR, (AHMEDABAD), Your Confirmation Attached”

  4. Copy subject line in body like hard enter after buyer name like

    “11345-SENATOR, (AHMEDABAD),

    Your Confirmation Attached’



    I have formatted signature which require to put in that mail & send

  5. Finally , Of course for each file send mail for all files in that folder
Can it be possible through excel VBA?

Any help will be appreciated..

Regards,

Chirag Raval
 
Dear All,

We can get name for particular buyer no as below code from Excel file attached (Buyer Master) ..but how to use it in Outlook?

Also require 3rd yellow highlighted column (Please refer attached buyer master) for "City" (like above mentioned..) of that buyer with their name & also prefix "M/s." after Buyer No.


"11345-M/s. SENATOR, (AHMEDABAD), Your Confirmation Attached”

Code:
Sub Formula_Result_In_Variable_Useit()
        Dim S$
    With [C4]
        .Formula = "=VLOOKUP(A2,'C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!$H:$I,2,FALSE)"
        S = .text
        .Value = ""
    End With
'        Debug.Print S
ActiveSheet.range("B2").Value = S
End Sub

50 files , path is below
"H:\WINT-17\1-W-17-ALL ORIGINAL FROM MILL\W-17-CONFIRMATION"

hope I provide all sufficient detail of my requirement for get this code.
if you require to get more detail, please post or reply...

Regards,
Chirag Raval
 
Dear Sir @Chihiro,

Thanks for reply.
I already study that page but its for send only 1 file,
My requirement (mostly all user"s require that's natural) is above .
Send all file IIn Folder with unique subject - body with
Signature.l that require loop for each file
And need some more technical presise coding

Hope you can understand.

Regards,

Chirag Raval
 
As always upload sample file. Hard to code anything without knowing structure of your workbook.
 
As requested, attach short sample master file by clicking "Upload a File", it makes it easier to help. Obviously, columns H and I would be needed. Since that is an xlsx file, would you play the macro from some other workbook? Would the xlsx file be open already.

As for the lookup for the name in the master file, the better method would be to use a range find.

You do have to consider, what if the lookup/find fails? The 5 digit file prefix name might exist but the match for the name may not.

For the signature thing, Ron de Bruin, has one method. I prefer another. I find it easier if the signature is in an Excel range. Even so, once you see my methods, you can change to suit.

For the filenames to attach, are these type: xlsx, txt, docx, etc.?

My methods:
1. Use my stanadard aFFs() command shell Dir() method to quickly get the filenames to attach.
2. Use range find to find the offset value in master file to build the subject string with each files 5 digit prefix filename.
3. Create the outlook item to send.

Of course it is assumed that you have the 5 digit group names setup in Outlook already.
 
Dear Sirs @Kenneth Hobson & @Chihiro,

Many thanks for your co-operation rearding this thread..

I attached Suiting Buyer Master.xlsx thats need to temporarily open in background just for lookup purpose only.. for lookup names for 5 digits buyer No. & City Name with yellow highlited columns "H", "I" & "J"
We can modify buyer master if number's file found in folder but name not found in buyer master..thats matter of "If Error- Resumen Next" because
mail should not be stop sending on error we can lastly display message "45 Files Send Out of 50 Found in folder"...more precisly we can display message with buyer no which could not be send .....

My Signature is in "Rich Text Format" so we can put the path for that..

We can attache any file to the mail but mostly type is "*.xlsx"

Hope we all are near some solution..

Please reply if needed more detail to resolve..

Thanks again to all of your support.

Regards,

CHirag Raval
 

Attachments

  • SUITING-BUYER MASTER.xlsx
    57.5 KB · Views: 13
You should be use to my aFFs() routine now so nothing new there.

Change the INPUTS area in Main() to suit.

Add the 2 references (Outlook and Word) in VBE's menu as commented.

The last part to add is the signature "rtf". Range copy is easy. I am not sure if you want to copy from doc, docx, txt, rtf, or other file type. For now, just copy your signature rtf data and then run Main().

To block select to copy, I like to run OneNote once. Then Win+S or Win+Shift+S will let you copy a snippet to the clipboard from then on. Or, Win+R > Snipping Tool > Enter Key, can also be used to copy a rectangular area snippet.

Note that since I used a range find, if you have leading 0's for number formats, the real number does not have those. So, Val() is used to search. This can be a problem for some but it appears that you did not do that so it is a non-issue for you.

Code:
Sub Main()
  Dim T$, p$, a, e, r As Range, c As Range, pF$, S$
  Dim fso As Object, wb As Workbook, ws As Worksheet
  'Tools > References > Microsoft Outlook xx.0 Object Library > OK
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  'Tools > References > Microsoft Word xx.0 Object Library > OK
  Dim Word As Document, wr As Word.Range

  'INPUTS to change if needed...........................................................
  T = "ken@gmail.com"
  p = "H:\WINT-17\1-W-17-ALL ORIGINAL FROM MILL\W-17-CONFIRMATION\*.*"
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\SUITING-BUYER MASTER.xlsx", False, True)
  Set ws = wb.Worksheets("BUY MASTER")
  'End INPUTS...........................................................................

  Set fso = CreateObject("Scripting.FileSystemObject")

  'Batch to get all filenames.
  a = aFFs(p)
  If Not IsArray(a) Then Exit Sub

  'Get Outlook application
  Set olApp = New Outlook.Application

  'Iterate all elements in a, filenames to attach.
  For Each e In a
    'Get 5-digit prefix of base filename.
    pF = fso.GetBaseName(e)
    If Len(pF) < 5 Then GoTo NextE
    pF = Left(pF, 5)

    'Find matching prefix "number" in usedrange G:H
    Set r = ws.Range("G1:H" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row).Find _
      (Val(pF), ws.[G1], , xlWhole)
    If r Is Nothing Then GoTo NextE

    'Set subject value:
    S = pF & "-" & ws.Cells(r.Row, "I").Value & ", (" & ws.Cells(r.Row, "J").Value & ")"

    'Make email, attach file, and  send/display.
    'Set olMail = olApp.CreateItem(olMailItem)
    With olApp.CreateItem(olMailItem)
      .Importance = olImportanceNormal
      .To = T
      .Subject = S
    
      'Add 1st part of body.
      .GetInspector.Display
      Set Word = .GetInspector.WordEditor
      Word.Content = S & "," & vbCrLf & vbCrLf & _
        "Your Confirmation Attached" & vbCrLf & vbCrLf
  
      'Paste signature at end of body.
      Set wr = Word.Content
      wr.Collapse Direction:=wdCollapseEnd
      wr.Paste  'Paste at end

      .Attachments.Add e
      'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._mailitem.deferreddeliverytime.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
      '.DeferredDeliveryTime = Now + TimeValue("00:10:00")
      .Display
      '.Send
    End With
NextE:
  Next e

TheEnd:
  Set olMail = Nothing
  Set olApp = Nothing
  Set ws = Nothing
  wb.Close False
  Set wb = Nothing
End Sub

'Set extraSwitches, e.g. "/ad", to search folders only.
'MyDir should end in a "\" character unless searching by wildcards, e.g. "x:\test\t*
'Command line switches for the shell's Dir, http://ss64.com/nt/dir.html
Function aFFs(myDir As String, Optional extraSwitches = "", _
  Optional tfSubFolders As Boolean = False) As Variant

  Dim S As String, a() As String, v As Variant
  Dim b() As Variant, I As Long

  If tfSubFolders Then
    S = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b /s " & extraSwitches).StdOut.ReadAll
    Else
    S = CreateObject("Wscript.Shell").Exec("cmd /c dir " & _
      """" & myDir & """" & " /b " & extraSwitches).StdOut.ReadAll
  End If

  a() = Split(S, vbCrLf)
  If UBound(a) = -1 Then
    Debug.Print myDir & " not found.", vbCritical, "Macro Ending"
    Exit Function
  End If
  ReDim Preserve a(0 To UBound(a) - 1) As String 'Trim trailing vblfcr

  For I = 0 To UBound(a)
    If Not tfSubFolders Then
      S = Left$(myDir, InStrRev(myDir, "\"))
      'add the folder name
      a(I) = S & a(I)
    End If
  Next I
  aFFs = sA1dtovA1d(a)
End Function

Function sA1dtovA1d(strArray() As String) As Variant
  Dim varArray() As Variant, I As Long
  ReDim varArray(LBound(strArray) To UBound(strArray))
  For I = LBound(strArray) To UBound(strArray)
    varArray(I) = CVar(strArray(I))
  Next I
  sA1dtovA1d = varArray()
End Function
 
Last edited:
Same as the last Main() but has the sig.rtf added for the copy as signature to body of email.
Code:
Sub Main()
  Dim T$, p$, a, e, r As Range, c As Range, pF$, S$, sig$
  Dim fso As Object, wb As Workbook, ws As Worksheet
  'Tools > References > Microsoft Outlook xx.0 Object Library > OK
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  'Tools > References > Microsoft Word xx.0 Object Library > OK
  Dim Word As Document, wr As Word.Range
 
  'INPUTS to change if needed...........................................................
  T = "ken@gmail.com"
  'p = "H:\WINT-17\1-W-17-ALL ORIGINAL FROM MILL\W-17-CONFIRMATION\*.*"
  p = "C:\Users\lenovo1\Dropbox\Excel\Outlook\emailPDFinvoices\*.*"
  Set wb = Workbooks.Open(ThisWorkbook.Path & "\SUITING-BUYER MASTER.xlsx", False, True)
  'Windows(wb.Name).Visible = False
  Set ws = wb.Worksheets("BUY MASTER")
  'File to copy content as signature for body of email.
  sig = ThisWorkbook.Path & "\sig.rtf"
  'End INPUTS...........................................................................
 
  Set fso = CreateObject("Scripting.FileSystemObject")
 
  'Batch to get all filenames.
  a = aFFs(p)
  If Not IsArray(a) Then Exit Sub
 
  'Get Outlook application
  Set olApp = New Outlook.Application
 
  'Iterate all elements in a, filenames to attach.
  For Each e In a
    'Get 5-digit prefix of base filename.
    pF = fso.GetBaseName(e)
    If Len(pF) < 5 Then GoTo NextE
    pF = Left(pF, 5)
   
    'Find matching prefix "number" in usedrange G:H
    Set r = ws.Range("G1:H" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row).Find _
      (Val(pF), ws.[G1], , xlWhole)
    If r Is Nothing Then GoTo NextE
   
    'Set subject value:
    S = pF & "-" & ws.Cells(r.Row, "I").Value & ", (" & ws.Cells(r.Row, "J").Value & ")"
   
    'Copy content from sig.rtf as signature for body of email to clipboard.
    GetObject(sig).Range.Copy
   
    'Make email, attach file, and  send/display.
    'Set olMail = olApp.CreateItem(olMailItem)
    With olApp.CreateItem(olMailItem)
      .Importance = olImportanceNormal
      .To = T
      .Subject = S
       
      'Add 1st part of body.
      .GetInspector.Display
      Set Word = .GetInspector.WordEditor
      Word.Content = S & "," & vbCrLf & vbCrLf & _
        "Your Confirmation Attached" & vbCrLf & vbCrLf
     
      'Paste signature at end of body.
      Set wr = Word.Content
      wr.Collapse Direction:=wdCollapseEnd
      wr.Paste  'Paste at end
 
      .Attachments.Add e
      'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._mailitem.deferreddeliverytime.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
      '.DeferredDeliveryTime = Now + TimeValue("00:10:00")
      .Display
      '.Send
    End With
NextE:
  Next e
 
TheEnd:
  Set olMail = Nothing
  Set olApp = Nothing
  Set ws = Nothing
  wb.Close False
  Set wb = Nothing
End Sub

Sub EmbedClipboardAndAppendClipboardInBody()
  Dim ws As Worksheet, r As Range, b As Range
  'Tools > References > Microsoft Outlook xx.0 Object Library
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  'Tools > References > Microsoft Word xx.0 Object Library
  Dim Word As Document, wr As Word.Range
 
  Set ws = Worksheets("Renewal Dates")
  Set r = ws.Range("A1:H1")
 
  Set olApp = New Outlook.Application
  Set olMail = olApp.CreateItem(olMailItem)
 
  r.AutoFilter 7, ">=" & Date
  r.AutoFilter 8, "<>x"
  Exit Sub
 
  With olMail
    .Importance = olImportanceNormal
    .To = "ken"
    .Subject = "31 Day Reminder"
     
    .GetInspector.Display
    Set Word = .GetInspector.WordEditor
   
    r.Copy
    Word.Range(0, 0).Paste
    r.Offset(2).Copy
    Set wr = Word.Content
    wr.Collapse Direction:=wdCollapseEnd
    wr.Paste  'Paste at end
    Application.CutCopyMode = False
   
    'https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook._mailitem.deferreddeliverytime.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
    .DeferredDeliveryTime = Now + TimeValue("00:10:00")
    .Display
    '.Send
  End With
 
TheEnd:
  Set olMail = Nothing
  Set olApp = Nothing
End Sub
 
Dear all,

Very sorry to late reply ..
I am also egarly waiting to check both above
Macros..but as I open your code in pc ,
Someone coming to me with some more work for me,
Or phone ringing for me for more workwork,
Or other situations created that since your code
I already copied in my pc , till now I can not check
That both valuable (like Gold) codes.

But definetly situation change soon for me
to check & I can reply what I achieve after run this Botha macros.

Thank you very much to all experts & users for
Efforts for me.

I will revert soon

Regards,

Chirag Raval
 
Daer Sir @Kenneth Hobson ,

Just amazing, Great Work...really appreciated..
many thanks for your never before miracle macro..

2 points there

(1) I will check your second macro & soon revert
(2) Can we use Contact Group in "To" field?
Because I creatd separate groups for each buyer
& group name is also 5 digits Buyer Number.
Like group name 11345 can 1, 2 or more E-Mail Ids
So if this macro can search olso for that 5 digits groups,
& if found, put it in "To" field, mail for that buyer send to it based on attached file,s first 5 digits & of course, subject & body also related to that buyer already create by your macro but I don't know where are
Groups stored in Outlook.

Your little help as just 1 effort can save thousands of efforts of
My (Definetly also of other seekers) to do ,repeat same process again & again.

Hope your help.

Regards,

Chirag Raval
 
Last edited:
Not sure what to check on (1). My mouse battery was weak so it copied the "2nd macro," not used, inadvertently.

For (2), there is no reason to check for group names in Outllook unless you want to send to someone else if pf does not exist. Without looking at data again, I think PF is what you meant?
Code:
.To = pf
 
Dear Sir @Kenneth Hobson

Yes, perfect..amazing ...miraculous...

But how can get rid from ask permission on each
Sending..its always ask for Accept- Allow or denied or cancel.
Outlook say this is programmatically sending
& may be some other application send message on behalf of you without notice to you..Allow or denied also says your antivirus software
Not updated...& alert have also a progress bar which tack about 5 seconds on each send to complete after your Allow or Denied.

Actually all this testing done on my home pc
There are no internet - no any antivirus software installed
In it.. So may be live internet can explore some possibility to unlock grayout options which i can not change.

Please not that this is just assumption of relation between this alert & live internet may be i am wrong...I will go office tomorrow then check
On live internet ..& with antivirus software.

I also checked security options trust center & othet settings but I. Cannot found
Anything that how can we stop this alert on evey sending..

Please also note that each mail instance
Not generate from main outlook application.
If outlook closed before this macro do its job,
We can see only independent mailed without
Main application opened ..is this effact on main application's internal data maintain inviroment? Or Its necessary to open main application through code before sendingsending any mail?

Please help

Regards,

Chirag Raval
 
Last edited:
A Send will fail if say you send a group email and it does not exist in your addressbook.

There are too many anti-virus programs for me to know which is a problem. Even Windows Defender might be an issue. For security reasons like these, some prefer CDO.

Outlook opens if Display is called. If Send and closed, mail goes into Outbox and is sent when you open it.

If you are using Gmail's server to Send, you must lower your security in Gmail settings, not Outlook.

Here are my notes for Gmail lower security:
'CDO to gmail requires lowering your security:
'https://myaccount.google.com/security#connectedapps
'at the end set, Allow less secure apps: ON
 
Dear Sir @Kenneth Hobson,

Your code is great..thank you very much

My rtf signature start from "Dear Sir"
Then 2 or 3 blank Enter (vbCRlf)
Then my name & other things..

Requirement is that above your code's body matter
Buyer no & " Your confirmation attached"
Put after (below) my signature's "Dear Sir"
In other words your code"s
Word .contentscontents = S & "-" & ........"
Should be put between pre exist by signature
"Dear Sir," and my name to rest other mattersmatters

In other wordswords

"Dear Sir " (already exist in my signaturesignature)

11345- Senator , Ahmedabad
Your Confirmation Attached

Regards,
My name ,
Firm name, & Contact detail..

Hope I can describe well my requirement.

Regards,

Chirag Raval
 
I have never heard of a signature block at the beginning.

Maybe post where copy/paste signature block from sig.docx goes in red and just say SIG.DOCX. For the built string "S" which is same as Subject, just put the letter "S" in Pink. For the concatenated strings but in black bold.

You should be able to do this yourself. It is just a matter of doing it in a different order with different concatenated strings and docx copy/paste parts. As I said, for standard/"boiler plate" text, put it into a cell or better yet a named range, or even a docx file, reduces the need for lots of string concatenation.
 
Dear Sir @Kenneth Hobson ,

Very sorry but I can't understand ..above your guideline..I have sig.rtf...
I can not find SIG.DOCX. , "S" in Pink. & black bold.

can you Re-describe as understandable format..?
OR is below can be more simple to do?


can you modify in code that first Signature drop in mail body,
if signature present in mail body then first put the cursor in last of "Dear Sir" then 3 times cursor down & then put variable "S" before my name & firma detail?

Regards,
Chirag Raval
 
Now it sounds like you are wanting to use the signature from Outlook? There is more than one type and can be several.

As I explained before, there are 3 types of Bodies in Outlook.
1. Body, plain text.
2. HTMLbody, html text.
3. WordEditor, can make use of some Word object methods.

The signature data or any data, would be in a RTF file, DOCX, or such. Add to it whatever you like. Include graphics, formatted text, etc. There can even be more files with content copy/pasted into WordEditor.

WordEditor Body, example:
sig.rtf

11345- Senator , Ahmedabad
Your Confirmation Attached

Regards,
My name ,
Firm name, & Contact detail..
 
Dear Sir @Kenneth Hobson ,

Thank you very much for your reply with
Helping words & appreciated knowledge.

Actually I pass 2 days to get just above suggested
Resul through googling.

I try to understand outlook VBA...
But it looks some hard to understand at my level.

Now to the point in signature how to put
Variable "S" 's value? Exactly I want what you mentioned.
How It will be change dynamically at every sending?

Now matter mixup as outlook' signature and excel's
Variable

Please helphelp

Regards,

Chirag Raval
 
I don't understand.

Signature is A or B?
A. Default from Outlook as it does for New Email.
or
B. Signature from a sig.rtf or sig.docx file or other external file.

S is the dynamic string for the body which I showed in hot pink.
11345- Senator , Ahmedabad
 
Dear Sir @Kenneth Hobson

Of course option B, due to I can modify what I want In it..

Actually outlook 's default signature is for my regular other mails,
but if I want to run this special macro, for special requirement,
that's signature is from Rich Text Format..due to it can be change any time anywhere as requirement..

hope your co-operations..

Regards,
Chirag Raval
 
Dear Sir @Kenneth Hobson ,


There are another matter raised, I have same name Contact Group multiple times, in Suggested contacts & contacts. (may be both required) i don't know
from which outlook seek for contacts or contacts group..

each sending display dialog box that same group name multiple times from which group name you want to send. each sending must be click on "ok"
as selected default ordered 1st group name..

if there are code for overcome this , that if found more then one Contact group of same name (regardless its found from contact or suggested contacts) , its take default 1st from displayed list without prompt anything. for code run smoothly..

thank you very much ..for your help since this start from this thread.

(1) post no 24 is my answer for your question which wait from your side
something important. for RTF signature

(2) My this post no 25 is another factor of same thread , also wait for
modification in code. for contact group

Regards,
Chirag Raval
 
Back
Top