Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2011
    Posts
    14

    Send email, where body equals same row column b as address in column a

    Hello I need to send a number of emails on a dailey bases, but I need the body of the email to contain items in the cells adjacent to the email address.
    example of my spreadsheet: Start in cell A1 of course
    Email Shop ClientId Endorsment#
    testsubject1@email,com TestSubject1 123 12345
    testsubject2@email,com TestSubject2 234 23456
    testsubject3@email,com TestSubject3 345 34567
    testsubject4@email,com TestSubject4 456 45678
    testsubject5@email,com TestSubject5 789 56789
    I have a code that allows me to send an email to every address in column A but the subject and body are static and sent to everyone the same. What I need is the email sent to TestSubject1 to include ClientId = 123 and Endorsment# = 12345 and TestSubject2's email to include only Client = 234 and endorsment# = 23456 and so on. The code I have that will send the email is
    Code:
    Sub send_email()
    
    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient As Variant, vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
    Dim count As Long
    Set noSession = CreateObject("Notes.Notessession")
    Set noDatabase = noSession.GETDATABASE("", "")
    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    Set noDocument = noDatabase.CREATEDOCUMENT
    'Get Email address(s)
    vaRecipient = Worksheets("Sheet1").Range("A1:A1000")
    
    vaSubject = "TEST"
    vaMsg = "Testing"
    noDocument.SendTo = vaRecipient
    
    noDocument.CopyTo = vaCopyTo
    noDocument.Form = "Memo"
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.Subject = vaSubject
    noDocument.Body = vaMsg
    noDocument.SAVEMESSAGEONSEND = True
    
    With noDocument
    .PostedDate = Now()
    .SEND 0, vaRecipient
    End With
    
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
    
    'Activate Excel for the user.
    
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    326
    Ok, I've never used Lotus so a lot of your code is alien to me but I assume it works along the same lines as Office. I have a similar setup to you in fact but rather than assign a range of "A1:A1000", I asign the range to a single cell "A1", send the email based on that cell and then loop continually using Range = Range.Offset(1,0) until range<>"".

    So, merging my code with your code, I get something like this. If it doesn't work I'm sorry but the method should so you may just need to tweak it.

    Note: These are my changes to your Code.
    1. Dim vaRecipient As Range
    2. Do while Range <> ""
    3. vaSubject = vaRecipient.Offset(0,2) 'Move 2 cells to the right of the set range
    4. vaMsg = vaRecipient.Offset(0,3) 'Move 3 cells to the right of the set range
    5. vaRecipient = vaRecipient.offset(1,0) 'Set the range as 1 row down from the last

    Code:
    Sub send_email()
    
    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient As Range, 
    Dim vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
    Dim count As Long
    Set noSession = CreateObject("Notes.Notessession")
    Set noDatabase = noSession.GETDATABASE("", "")
    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    Set noDocument = noDatabase.CREATEDOCUMENT
    
    'Get Email address
    vaRecipient = Worksheets("Sheet1").Range("A1")
    
    Do While vaRecipient <> ""
    
    vaSubject = "Client ID: " & vaRecipient.Offset(0,2)
    vaMsg = "Endorsement: " & vaRecipient.Offset(0,3)
    
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.Form = "Memo"
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.Subject = vaSubject
    noDocument.Body = vaMsg
    noDocument.SAVEMESSAGEONSEND = True
    
    With noDocument
    .PostedDate = Now()
    .SEND 0, vaRecipient
    End With
    
    vaRecipient = vaRecipient.Offset(1,0)
    
    Loop
    
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
    
    'Activate Excel for the user.
    
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    
    End Sub
    Last edited by christyxo; 03-02-12 at 09:12. Reason: Said 'look'. Meant 'Loop'

  3. #3
    Join Date
    Apr 2011
    Posts
    14
    Thank you so much for the response christy, I am having some trouble getting it to work though, I get an "Object variable or With block variable not set" error, at vaRecipient, I changed vaRecient to variant instead of range and that error goes away but then I get an "Object Required" error at vaSubject. you have any thoughts on this? again thanks you so much for your assistance, you guys at this forum are awesome.

    Code:
    Sub send_email001()
    
    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient As range
    Dim vaMsg As Variant, vaSubject As Variant, vaCopyTo As Variant
    Dim count As Long
    
    Set noSession = CreateObject("Notes.Notessession")
    Set noDatabase = noSession.GETDATABASE("", "")
    
    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    
    Set noDocument = noDatabase.CREATEDOCUMENT
    
    'Get Email address
    vaRecipient = Worksheets("Sheet1").range("A1")
    
    Do While vaRecipient <> ""
    
    vaSubject = "Client ID: " & vaRecipient.Offset(0, 2)
    vaMsg = "Endorsement: " & vaRecipient.Offset(0, 3)
    
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.Form = "Memo"
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.subject = vaSubject
    noDocument.body = vaMsg
    noDocument.SAVEMESSAGEONSEND = True
    
    With noDocument
    .PostedDate = Now()
    .SEND 0, vaRecipient
    End With
    
    vaRecipient = vaRecipient.Offset(1, 0)
    
    Loop
    
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
    
    'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    
    End Sub

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    326
    I'm sorry - I wrote that all up in notepad last night. I can't test it without lotus either so sorry that you have to do a little tweaking.

    What about if you assign Dim vaSubject as String?

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    326
    Oh sorry! And I think I know the reason for the initial error. I never setthe range.

    Keep the Dim vaRecipient as Range

    But change
    Code:
    vaRecipient = Worksheets("Sheet1").range("A1")
    to

    Code:
    Set vaRecipient = Worksheets("Sheet1").range("A1")
    See if that has any impact.

    i.e.

    Code:
    Sub send_email001()
    
    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient As range
    Dim vaMsg As Variant, vaCopyTo As Variant
    Dim vaSubject As String
    Dim count As Long
    
    Set noSession = CreateObject("Notes.Notessession")
    Set noDatabase = noSession.GETDATABASE("", "")
    
    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
    
    Set noDocument = noDatabase.CREATEDOCUMENT
    
    'Get Email address
    Set vaRecipient = Worksheets("Sheet1").range("A1")
    
    Do While vaRecipient <> ""
    
    vaSubject = "Client ID: " & vaRecipient.Offset(0, 2)
    vaMsg = "Endorsement: " & vaRecipient.Offset(0, 3)
    
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.Form = "Memo"
    noDocument.SendTo = vaRecipient
    noDocument.CopyTo = vaCopyTo
    noDocument.subject = vaSubject
    noDocument.body = vaMsg
    noDocument.SAVEMESSAGEONSEND = True
    
    With noDocument
    .PostedDate = Now()
    .SEND 0, vaRecipient
    End With
    
    vaRecipient = vaRecipient.Offset(1, 0)
    
    Loop
    
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
    
    'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
    
    End Sub

  6. #6
    Join Date
    Apr 2011
    Posts
    14
    Almost, now I am getting a "Notes error: No names found to send mail to"
    and when I debug it is in the .Send 0, vaRecipient section, I tried changing it to just .Send = vaRecipient, and also just took the line out completly and it goes into running mode and stays there until I get impatient and hit esc. Thank you so much for trying to help me though, I will keep scouring the web until I either fix it or give up lol

  7. #7
    Join Date
    Apr 2011
    Posts
    14
    I found a solution finally, never did get it to work in vba, but if anyone else is having this similar issue GO HERE Even more awesome LotusScript mail merge for Lotus Notes + Microsoft Excel | sacha chua :: living an awesome life
    And thank Sacha Chua for her super awesome mail merge script.

Posting Permissions

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