Outlook Mail comparison from two different forms

[ijpbs]

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.

[arnelgp]

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

[ijpbs]

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

[arnelgp]

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

[ijpbs]

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)

[ijpbs]

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

[ijpbs]

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
28967

[arnelgp]

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

[ijpbs]

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

[arnelgp]

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

[ijpbs]

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

[ijpbs]

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.

[arnelgp]

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

[ijpbs]

Thank you sir. Its working fine