Excel - Enviar correo desde Excel a un destinatario variable

Hola, tengo una macro para enviar correos de manera automática después de buscar hacer una búsqueda. El número buscado se compone de 3 partes, la primera parte es el número de la oficina (vamos a llamarle registro_oficina.value, situado en la hoja 1).

No se como hacer para que cuando detecte la oficina automáticamente sepa que correo tiene.
Las oficinas/correos están en la hoja 2. Las oficinas esta en la columna A1 y los correos en la B1

Gracicias

Si el codigo de oficina es unico
en la hoja 1 has un buscarv con el codigo de officina en la hoja 2 y obtienes los datos para enviar el correo


en la hoja 1

Espero te ayude

Con buscar v no me funciona, he conseguido que funcione con indice y coincidir pero a la hora de pasarlo a VBA no entiendo muy bien lo que pone y no se muy bien como cambiarlo.

Necesitaria que en vez de la celda G5 el dato buscado sea en un textbox situado en la hoja 1 llamado registro_oficina, creo que justamente seria esta parte de aqui .

Cuando se da al boton buscar se abre un formulario con los resultados obtenidos y hay un boton para enviar un correo a la oficina de manera automatica, necesitaria que cuando buscara el dato tambien me buscara el correo de la oficina.