Consulting

Results 1 to 14 of 14

Thread: Outlook Mail comparison from two different forms

  1. #1
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location

    Outlook Mail comparison from two different forms

    Hi Team,

    We are having two forms(Form 1 and Sub Forms 1), need to compose mail with comparing different names of table field names.


    ie., Form 1 - Ms no
    Sub Forms 1 - Undergoing MS
    ( But both Ms no and Undergoing MS are same)


    On comparing both we need to get the email id from Sub Forms 1 and send mail in outlook.


    Below is the vba code am using to fetch the mail id from one table. With this i need the above scenario


    Dim olApp As Object
     Dim olMail As Object
     Dim strPath As String
     Dim strLocation As String
     Set olApp = CreateObject("Outlook.Application")
     Set olMail = olApp.CreateItem(0)
     Dim strsubject As String
     Dim sMsgBody As String
     Dim a As String
        M = Me![Email id]
        'b = Me![Status Msg]
    a = Me![MS no]
        'MsgBox a
        T = Me![Title]
        'MsgBox T
        'SB = Me![Subandbranch]
    With olMail
        strsubject = "Received your manuscript for publication, www.ijpbs.net"
        sMsgBody = "Respected author,"
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "Ref manuscript number  MS: " & a & ""
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "We have received your research/review article “" & T & "” and the copy right form and would be categorized under " & SB & ". We have allotted manuscript number MS " & a & " for your article. For further correspondence, kindly use this manuscript number only. We would be sending your manuscript to our reviewer (Reviewer 1) for evaluation. We will send the acceptance decision within 7-10 working days"
        sMsgBody = sMsgBody & "<br><br>"
       .To = M
       .Subject = (strsubject)
       .HTMLBody = (sMsgBody)
       '.Attachments.Add "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\link.txt"
       ' strLocation = "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\MS " & a & " RAW.docx"
       '.Attachments.Add (strLocation)
    .Display
    End With
    Set olMail = Nothing
    Set olApp = Nothing

    Kindly help me in this issue.
    Attached Images Attached Images
    Last edited by Aussiebear; 02-18-2022 at 01:30 PM. Reason: Added code tags to supplied code

  2. #2
    '=======Code=========
    Dim olApp As Object
    Dim olMail As Object
    Dim strPath As String
    Dim strLocation As String
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    Dim strsubject As String
    Dim sMsgBody As String
    Dim a As String
    
    
    Dim i As Integer
    Dim v As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    'Replace yourTable with the name of the table that has Undergoing field"
    Set rs = db.OpenRecordset( _
            "select [email id] from yourTable where Undergoing = '" & Me![MS No] & "'", dbOpenSnapshot, dbReadOnly)
                    
    'm = Me![email id]
    'b = Me![Status Msg]
    
    
    a = Me![MS No]
    'MsgBox a
    T = Me![Title]
    'MsgBox T
    'SB = Me![Subandbranch]
    With olMail
    
    
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst
        End If
        Do Until rs.EOF
            v = Split(rs![email id], ",")
            For i = 0 To UBound(v)
                v(i) = Trim$(v(i))
                If Len(v(i)) Then
                    .Recipients.Add v(i)
                End If
            Next
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        
        strsubject = "Received your manuscript for publication, www.ijpbs.net"
        sMsgBody = "Respected author,"
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "Ref manuscript number MS: " & a & ""
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "We have received your research/review article “" & T & "” and the copy right form and would be categorized under " & sb & ". We have allotted manuscript number MS " & a & " for your article. For further correspondence, kindly use this manuscript number only. We would be sending your manuscript to our reviewer (Reviewer 1) for evaluation. We will send the acceptance decision within 7-10 working days"
        sMsgBody = sMsgBody & "<br><br>"
        .To = m
        .Subject = (strsubject)
        .HTMLBody = (sMsgBody)
        '.Attachments.Add "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\link.txt"
        ' strLocation = "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\MS " & a & " RAW.docx"
        '.Attachments.Add (strLocation)
        
        
        .Display
    End With

  3. #3
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Thanks for the code--

    Error after replacing table name

    'Replace yourTable with the name of the table that has Undergoing field"
    Set rs = db.OpenRecordset("select [Email id] from profile details form updated where Undergoing MS = '" & Me![MS no] & "'", dbOpenSnapshot, dbReadOnly)

    Error

    Error 3131, Syntax error in from clause


  4. #4
    if there is a "space" on your field/table name, enclosed it in square bracket.
    make sure it is the Table name, not the Form name.

    Set rs = db.OpenRecordset("select [Email id] from [profile details form updated] where Undergoing MS = '" & Me![MS no] & "'", dbOpenSnapshot, dbReadOnly

  5. #5
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Its the table name 'profile details form updated' only

    Error - 3061 - Too few parameters. Excepted 1

    Dim a As String
    a = Me![MS no]
    Set rs = db.OpenRecordset("select [Email id] from [profile details form updated] where [Undergoing MS] = '" & a & "'", dbOpenSnapshot, dbReadOnly)

  6. #6
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Thanks a lot , code is working fine. Thank you very much. Great Job

  7. #7
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Hi,

    First Query

    Code is working fine. As of now if the value of [Undergoing MS number] is in single value its getting all the email id correctly.

    But in my database the value of [Undergoing MS number] will be like "11085,11061,11478" - comma separated. In comma separated mail id is not returning. Its showing null database.

    so need the code with comma separated of fetching the result.

    a = Me![MS no]
    Set rs = db.OpenRecordset("select [Email id] from [profile details form updated] where [Undergoing MS number] = '" & a & "'", dbOpenSnapshot, dbReadOnly)

    Second Query

    i need to get the values from other table. For ex

    T = Me![Title] - but Title field is not in the database of Me!(Current database).

    So how to assign the values from other tables.

    Kindly help
    Table-02.jpg

  8. #8
    you need a Split() function to split them into individual [Undergoing MS number]

  9. #9
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    i have very little knowledge in coding, so kindly share the code for split () function. it will be helpful for me

  10. #10
    1. change to:
    a = Me![MS no]
    Set rs = db.OpenRecordset("select [Email id] from [profile details form updated] where [Undergoing MS number] Like '*" & a & "*'", dbOpenSnapshot, dbReadOnly)

    2. you google DLookup(), to return a field from different table.

    T = DLookup("[fieldToReturn]" , "[otherTableName]", "[Condition]")

  11. #11
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Thanks a lot, thanks for your gentle and apt reply @arnelgp.

  12. #12
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Hi @arnelgp, kindly share the same script such that email id should be in 'BCC' copy, now its in 'To'

    .To = M its not working, even though if we remove it all mail id placed in 'To' field.

  13. #13
    change this portion to:
    '=======Code=========
    Dim olApp As Object
    Dim olMail As Object
    Dim strPath As String
    Dim strLocation As String
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    Dim strsubject As String
    Dim sMsgBody As String
    Dim a As String
    Dim sBCC As String
    
    
    Dim i As Integer
    Dim v As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    'Replace yourTable with the name of the table that has Undergoing field"
    Set rs = db.OpenRecordset( _
            "select [email id] from yourTable where Undergoing = '" & Me![MS No] & "'", dbOpenSnapshot, dbReadOnly)
                    
    m = Me![email id]
    'b = Me![Status Msg]
    
    
    
    
    a = Me![MS No]
    'MsgBox a
    T = Me![Title]
    'MsgBox T
    'SB = Me![Subandbranch]
    With olMail
    
    
    
    
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst
        End If
        Do Until rs.EOF
            v = Split(rs![email id], ",")
            For i = 0 To UBound(v)
                v(i) = Trim$(v(i))
                If Len(v(i)) Then
                    sBCC= sBCC &  v(i) & ";"
                End If
            Next
            rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        
    	If Len(sBCC) <> 0 Then
    		sBCC = Left$(sBCC, Len(sBCC)-1)
    	End If
    	
        strsubject = "Received your manuscript for publication, www.ijpbs.net"
        sMsgBody = "Respected author,"
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "Ref manuscript number MS: " & a & ""
        sMsgBody = sMsgBody & "<br><br>"
        sMsgBody = sMsgBody & "We have received your research/review article “" & T & "” and the copy right form and would be categorized under " & sb & ". We have allotted manuscript number MS " & a & " for your article. For further correspondence, kindly use this manuscript number only. We would be sending your manuscript to our reviewer (Reviewer 1) for evaluation. We will send the acceptance decision within 7-10 working days"
        sMsgBody = sMsgBody & "<br><br>"
        .To = m
        .Subject = (strsubject)
        .HTMLBody = (sMsgBody)
        '.Attachments.Add "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\link.txt"
        ' strLocation = "\\Dell-lptp\d\IJPBS process\Vol - 5, Issue 1\Downloads\Down1\MS " & a & " RAW.docx"
        '.Attachments.Add (strLocation)
        
        
        .Display
    End With

  14. #14
    VBAX Regular
    Joined
    Jul 2021
    Posts
    23
    Location
    Thank you sir. Its working fine

Tags for this Thread

Posting Permissions

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