#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16

    loop through customer file and send mail


    I have this question before can some one help
    How can I loop through a mail programme to send e_mail to customer

    I have workbook that runs against data file let say If my data file is Toyota_Q3-2003 it split the file name for customer name where is Toyota and put it in a cell then runs and produce 3 report for that customer Ex,
    Toyota Quarterly Q3-2003-All.xls
    Toyota Quarterly Q3-2003-customerl.xls
    Toyota Quarterly Q3-2003-error.xls

    If I run my work for other customers let say FORD or MAZDA it also produce reports for above customer
    Ford Quarterly Q3-2003-All.xls
    Ford Quarterly Q3-2003-customerl.xls
    Ford Quarterly Q3-2003-error.xls

    Mazda Quarterly Q3-2003-All.xls
    Mazda Quarterly Q3-2003-customerl.xls
    Mazda Quarterly Q3-2003-error.xls

    Question

    If I have another text file (cust_mail_address) that contain all e_mail address of above customers as Example
    Mazda mazda@Yahoo.com
    Toyota Toyota@yahoo.com
    Ford Ford@hotmail.com

    how can I use my mail programe to send each of above reports to the customers example all 3 report for customer FORD send to ford@hotmail.com and all 3 reports for Toyota reports to toyota@yahoo.com

    This is my mail program ( Iam usinf lotus notes mail)

    Sub Main
    Dim oSess As Object
    Dim oDB As Object
    Dim oDoc As Object
    Dim oItem As Object
    Dim direct As Object
    Dim Var As Variant
    Dim flag As Boolean

    Set oSess = CreateObject("Notes.NotesSession")
    Set oDB = oSess.GETDATABASE("", "")
    Call oDB.OPENMAIL
    flag = True
    If Not (oDB.ISOPEN) Then flag = oDB.OPEN("", "")

    If Not flag Then
    MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
    GoTo exit_SendAttachment
    End If
    On Error GoTo err_handler

    'Building Message
    Set oDoc = oDB.CREATEDOCUMENT
    Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
    oDoc.Form = "Memo"
    oDoc.subject = "This is the subject"
    oDoc.sendto = "FORD@hotmail.com"
    oDoc.body = "This is test text in the body of the email"
    oDoc.postdate = Date

    'Attaching DATABASE
    Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-customers.xls”)
    Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-All.xls”)
    Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-Errors.xls”)
    oDoc.visable = True
    'Sending Message
    oDoc.SEND False
    exit_SendAttachment:
    On Error Resume Next
    Set oSess = Nothing
    Set oDB = Nothing
    Set oDoc = Nothing
    Set oItem = Nothing
    'Done
    Exit Sub
    err_handler:
    If Err.Number = 7225 Then
    MsgBox "File doesn't exist"
    Else
    MsgBox Err.Number & " " & Err.Description
    End If
    On Error GoTo exit_SendAttachment
    End Sub
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    What's wrong when u complied your code??Plz give some wrong detail to us..
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16

    loop through custmer file and send mail


    There is noting wrong with send mail program it works as long as I hard code the customer mail address and hard code customer data file as I have shown in the program.
    MY question is
    I do not want each time to hard code the customer mail address and customer data file to send the mail, I want to know how can I make the send mail program more intelligent to send the mail for customers for given report or report produce for the customer, As previous I have mentioned.
    another word is that I do not want each time to edit the program and type the customer e_mail address and customer data file.
    HOW CAN I do this ???
    Last edited by josephg; October 14th, 2003 at 08:41 PM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    OK! I see!I recommend u to save the customer infomation into the db.If U don't save it into the db,U can save it into a text file.So When u will send mail,u can write code to read customer infomation to send mail with customer's mail address.
    U can use filesystem object in the vb to read or write text file.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    how can I do this could you please show me howto
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    Read Text From a File Using the File System sample:
    Private Function ReadTextFile(fName As String) As String

    Dim FSO As New FileSystemObject
    Dim FSTR As Scripting.TextStream
    Dim ret As Long
    If FSO.FileExists(fName) Then
    Set FSTR = FSO.OpenTextFile(fName)
    ReadTextFile = FSTR.ReadAll
    FSTR.Close
    Set FSTR = Nothing
    Else
    ret = MsgBox("File Not Found", vbCritical)
    End If
    Set FSO = Nothing
    End Function
    ' Using
    ' Private Sub Command1_Click()
    ' Text1.Text = ReadTextFile("c:\autoexec.bat")
    ' End Sub

    U can find it in the msdn..
  12. #7
  13. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    0
    if you want to save it to Access, and then use ADO to manipulate it, you can also declare an Access application by adding a reference to the Access library. The access object has a method called "copyfromexcel", or something similar to that. I've got the code at home, and can submit it after work if you want to see it. It allows you to populate a table with data from Excel, and works just like importing data from an external source in Access. I use it to set up temporary "staging" tables in Access applications.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    I am lost in dark
    ok I have tried your codes but I get error as follow
    Compiler error
    User defined type not defined

    As per your suggestion I have created a text file in c:\cust_mail.txt that contain

    Mazda mazda@Yahoo.com
    Toyota Toyota@yahoo.com
    Ford Ford@hotmail.com



    After all I do not know where to implement your codes with in my mail macro program, could you please advise.
  16. #9
  17. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    0
    if you're getting a user-defined type error, it's most likely because you didn't include the reference to the proper library - maybe the FSO library, or Outlook. Go to Project->References and check to make sure you have selected "file systems object" and "Outlook xx.x"(whatever version is on your machine)
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    32
    Rep Power
    16
    filesystem object is in the microsoft scripting runtime reference.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    Could you please show me with in my macro I have tried it but does not work

IMN logo majestic logo threadwatch logo seochat tools logo