Consulting

Results 1 to 11 of 11

Thread: Outlook Mail comparison from two different forms

  1. #1
    VBAX Regular
    Joined
    Jul 2021
    Posts
    15
    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

    =======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
    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

  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
    15
    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
    15
    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
    15
    Location
    Thanks a lot , code is working fine. Thank you very much. Great Job

  7. #7
    VBAX Regular
    Joined
    Jul 2021
    Posts
    15
    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
    15
    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
    15
    Location
    Thanks a lot, thanks for your gentle and apt reply @arnelgp.

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
  •