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.

Code:

---

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

---

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

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

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)

Thanks a lot , code is working fine. Thank you very much. Great Job

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
Attachment 28967

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

i have very little knowledge in coding, so kindly share the code for split () function. it will be helpful for me

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]")

Thanks a lot, thanks for your gentle and apt reply @arnelgp.

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.

change this portion to:

Code:

---

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

---

Thank you sir. Its working fine